In this blog post, we'll explore a suite of Python functions designed to simplify various tasks like creating, resuming, terminating and resizing workspaces, as well as uploading files to staging areas from S3.
Efficiently managing a database requires powerful tools. When coupled with Python, the SingleStore Management API provides an incredible level of control and automation. In this blog post, we'll explore a suite of Python functions designed to simplify various tasks like creating, resuming, terminating and resizing workspaces, as well as uploading files to staging areas from S3. Before we dive into the functions, ensure you have the following Python libraries imported:
1import requests2import json3import re4import boto35from datetime import datetime
For detailed API documentation, visit the SingleStore Management API Reference
Python-powered workspace management
Create workspaces with a simple function
Kickstart your database operations by setting up a new workspace effortlessly using Python.
1def create_workspace(name, size, workspaceGroupID, token):2 url = "<https://api.singlestore.com/v1/workspaces>"3 payload = {4 "enableKai": True,5 "name": name,6 "size": size,7 "workspaceGroupID": workspaceGroupID,8 }9 payload_json = json.dumps(payload)10 headers = {"Content-Type": "application/json", "Authorization": f"Bearer {token}"}11 response = requests.post(url, headers=headers, data=payload_json)12 return response.text
Tip: to get your workspace group ID, navigate to your workspace group and copy the last part of the URL.
.png?width=1024&disable=upscale&auto=webp)
Resuming a workspace
Need to get your suspended workspace back online? Do it in a snap with this function.
1def resume_workspace(workspaceID, token):2 url = f"<https://api.singlestore.com/v1/workspaces/{workspaceID}/resume>"3 headers = {"Content-Type": "application/json", "Authorization": f"Bearer {token}"}4 response = requests.post(url, headers=headers)5 return response.text
Tip: to get your workspaceID, click into the ellipsis (Actions) next to your workspace, click on “Edit Workspace Settings” and copy the workspace ID.
Terminating a workspace
Save resources by temporarily terminating inactive workspaces using this straightforward function.
1def terminate_workspace(workspaceID, token):2 url = f"<https://api.singlestore.com/v1/workspaces/{workspaceID}>"3 headers = {"Content-Type": "application/json", "Authorization": f"Bearer {token}"}4 response = requests.requests("DELETE", url, headers=headers)5 return response.text
Resizing a workspace
Adjust the size of your workspace easily to match your current needs, ensuring efficient resource utilization and performance optimization.
1def resize_workspace(workspaceID, size, token):2 url = f"<https://api.singlestore.com/v1/workspaces/{workspaceID}>"3 headers = {"Content-Type": "application/json", "Authorization": f"Bearer {token}"}4 payload = {5 "size": size6 }7 8 response = requests.patch(url, headers=headers, data=json.dumps(payload))9 10 return response.text
Tip: visit our sizing page for a list of supported sizes (i.e., S-00, S-1, S4, etc.)
Streamline file uploads to Stage
SingleStore Stage allows you to load files in a staging area attached to your workspace group, helping you quickly get your data into SingleStore (read more about it here).. You can either upload a local file, or upload a file in a S3 bucket.
Local file upload:
1def upload_to_staging(workspaceGroupID, stagePath, fileName, filePath, token):2 url = f"<https://api.singlestore.com/v1/stages/{workspaceGroupID}/fs/{stagePath}>"3 4 headers = {"Authorization": f"Bearer {token}"}5 6 # if do not want to specify fileName, only filePath7 # fileName = os.path.basename(filePath)8 9 files = [("file", (fileName, open(filePath, "rb")))]10 11 response = requests.request("PUT", url, headers=headers, files=files)12 13 return response.text
S3 file upload:
1def upload_to_staging(workspaceGroupID, bucketName, filepath, stagePath, awsKeyID, awsSecret, awsToken, token):2 url = f"<https://api.singlestore.com/v1/stages/{workspaceGroupID}/fs/{stagePath}>"3 4 headers = {"Authorization": f"Bearer {token}"}5 6 # accessing the S3 buckets using boto3 client7 s3 = boto3.client('s3',8 aws_access_key_id=awsKeyID,9 aws_secret_access_key=awsSecret,10 aws_session_token=awsToken11 )12 13 # download file from S314 s3.download_file(bucketName, filepath, 'localfile')15 16 # open the file in binary mode17 with open('localfile', 'rb') as f:18 files = {'file': f}19 response = requests.put(url, headers=headers, files=files)20 21 return response.text
Easy access to billing information
Staying on top of your billing information is crucial. With our Python function, accessing your billing details is just a function call away.
1def get_billing(startTime=None, endTime=None, aggregateBy="day"):2 # can change aggregateBy to "hour" or "month"3 4 # format time in ISO 8601 format5 def format_time(time):6 iso8601_pattern = re.compile(r"^\\d{4}-\\d{2}-\\d{2}T\\d{2}:\\d{2}:\\d{2}Z$")7 if iso8601_pattern.match(time):8 formatted_time = format_time9 else:10 try:11 parsed_time = datetime.strptime(time, "%Y-%m-%dT%H:%M:%SZ")12 formatted_time = parsed_time.strftime("%Y-%m-%dT%H:%M:%SZ")13 except ValueError:14 try:15 # if no time provided, add 'T00:00:00Z'16 parsed_date = datetime.strptime(time, "%Y-%m-%d")17 formatted_time = parsed_date.strftime("%Y-%m-%dT00:00:00Z")18 except Exception as e:19 raise ValueError(20 "Invalid date time format. Please provide a valid ISO 8601 formatted time."21 )22 23 return formatted_time24 25 formatted_startTime = format_time(startTime)26 formatted_endTime = format_time(endTime)27 28 url = f"<https://api.singlestore.com/v1/billing/usage?startTime={formatted_startTime}&endTime={formatted_endTime}&aggregateBy={aggregateBy}>"29 headers = {"Content-Type": "application/json", "Authorization": f"Bearer {token}"}30 31 response = requests.get(url, headers=headers)32 return response.text
This function will return a JSON object detailing the compute credits and storage usage for each workspace in the specified workspace group. You can then plot graphs to visualize — for instance, the compute credits consumed over time by each workspace. The following code snippet will aggregate this consumption by day:
1# plotting the compute credits consumed per day for each workspace2import matplotlib.pyplot as plt3import pandas as pd4from datetime import datetime5 6json_data = get_billing(startTime="2023-10-25", endTime="2023-10-28")7data = json.loads(json_data)8 9# function to parse the date-time format in the data10def parse_datetime(dt_str):11 return datetime.strptime(dt_str, '%Y-%m-%d %H:%M:%S %z UTC')12 13# complete data extraction14all_usage = []15for billing in data['billingUsage']:16 all_usage.extend(billing['Usage'])17 18# convert to dataframe19df_all = pd.DataFrame(all_usage)20 21# convert endTime, startTime, and value to appropriate types22df_all['endTime'] = df_all['endTime'].apply(parse_datetime)23df_all['value'] = pd.to_numeric(df_all['value'])24 25# extracting all distinct workspace names26all_resource_names = df_all['resourceName'].unique()27 28# creating a bar plot for each distinct workspace name29plt.figure(figsize=(15, 10))30 31# offset to position bars next to each other32bar_width = 0.04 # adjusting bar width for potentially large number of33resources34 35# plotting individual bars for each workspace36for idx, resource_name in enumerate(all_resource_names):37 # filter data for each workspace38 filtered_data = df_all[df_all['resourceName'] == resource_name]39 40 # group by date and sum the values41 grouped_data = filtered_data.groupby('endTime').agg({'value': 'sum'}).reset_index()42 43 # plotting44 plt.bar(45 grouped_data['endTime'] + pd.to_timedelta(idx * bar_width, unit='D'),46 grouped_data['value'],47 width=bar_width,48 label=resource_name49 )50 51# adjust x-axis to accommodate the bar width52plt.xticks(rotation=45)53plt.title('Compute Credits Consumed Per Day for Each Workspace')54plt.xlabel('Date')55plt.ylabel('Compute Credits')56plt.legend(title='Resource Name', bbox_to_anchor=(1.05, 1), loc='upper left')57plt.tight_layout()58plt.show()
You will get a plot that looks something like this:
Why use these Python functions?
These Python functions serve as practical examples of how to interact with the SingleStore Management API. They are designed to be:
- User friendly. Even if you're not familiar with the API, you can start using these functions right away.
- Timesaving. Automate repetitive tasks and focus on more important aspects of your work.
- Customizable. Feel free to modify these functions to better fit your specific requirements.
How to build an app with Python and SingleStore
Learn more
For a comprehensive understanding of what the SingleStore Management API offers, including detailed endpoint descriptions, check out the API Overview. You can put this all to the test by activating your SingleStore trial — including with our newly released Free Shared Tier.
The SingleStore Management API offers robust capabilities for managing your database environment. By leveraging these Python functions, you can automate and streamline your workspace operations and file management tasks. Whether you're adjusting workspace sizes, managing workspaces or handling file uploads, these tools are designed to enhance your productivity and simplify your database management journey.
