- Hello World
- Interactive Documentation
- Before You Begin
- Accessing Your Data
- Creating Self-Service Dashboards
- API Keys
- Metadata Store
- Importing and Running Projects Programmatically
- Keyboard Shortcuts
DataBlade provides an interactive environment for doing data analysis. After logging in, create a new Project and start typing some code in the Console in the bottom right part of the screen and see what happens.
a = 10b = [1,2,3]c = {"foo":"bar"}print "Hello World!"2 + 2To move any snippets from the Console into your Editor, hover over the code and click the icon that appears to the left of the code.
All of the DataBlade libraries and other Python docs are available via the DataBlade Console. For example:
Type data in the Console...
data...to see documentation for all DataBlade libraries:
datablade
DataBlade: Tools for working with data
Modules:
- adwords: Google AdWords
- bq: Google BigQuery
- fb: Facebook
- ftp: FTP
- ga: Google Analytics
...
To dive deeper, type a sub-library, such as data.fb...
data.fb...to see documentation for the DataBlade Facebook libraries:
datablade.fb
Facebook
Facebook objects may be accounts, campaigns, adsets, or ads -- so long as the
scoping makes sense: Accounts contain campaigns, campaigns contain adsets, and
adsets contain ads.
Functions to get objects and IDs:
- get_campaigns(integration_id, object_id): Gets campaigns in an object
...
If you're not seeing the documentation, make sure that the _ is selected in the Variable picker.
Before you begin using DataBlade, it's important to note that nearly all of the data querying libraries provided by DataBlade (data.*) return results as DataFrames. DataFrames are part of the popular pandas library and provide a very simple interface to common data manipulation tasks like filtering, mapping, merging, etc.
If you are not familiar with pandas, check out this introduction.
With that said, DataBlade provides you all the power and flexibility of a full-featured Python environment, so if you'd rather define your workflows using other techniques or libraries, you are absolutely free to do so. Here is a list of Python modules that are currently available to import in the environment. If you have additional libraries you'd like to see added, let us know.
- SQL
- User-Uploaded Files
- S3
- Google Analytics
- Google BigQuery
- Google AdWords
- Google Sheets
- MongoDB
- RethinkDB
- Salesforce
- FTP
- Shopify
- Other Data Sources
- Under "Data Integrations", set up your SQL integration. We currently support PostgreSQL, MySQL, Oracle, and Microsoft SQL Server (MySQL, Oracle, and MS SQL may still have some issues, so let us know if you run into any).
- Currently, we require that your database be accessible from external clients. In the future, we will have a solution for databases that are behind a firewall.
- Ensure that your database is set up to accept connections (whitelist) from our IP addresses:
52.25.129.138/32 104.198.5.177/32 104.196.226.127/32 - In your code, you can query your SQL using the following snippets:
# You can optionally provide a keyword argument index_col="column name" to set
# the DataFrame index. You'll often want to set this to your ID column name.
r = data.sql.query(INTEGRATION_ID, SQL_QUERY)
# You can also write a DataFrame back to your database
# if_exists takes three parameters:
# "fail": If table exists, do nothing
# "replace": If table exists, drop it, recreate it, and insert data
# "append": If table exists, insert data. Create if does not exist
data.sql.write(INTEGRATION_ID, TABLE_NAME, DATAFRAME, if_exists="fail")
# You can also update specific rows. ID refers to the specific row you
# want to update. VALUES is a dict mapping column name -> updated value.
# The return value is a Boolean indicating whether or not a row was successfully
# updated. False likely means the ID was not found.
row_was_updated = data.sql.update_row(INTEGRATION_ID, TABLE_NAME, ID, VALUES)
# You can also query any existing DataFrames that are in memory as if they were tables
# The DataFrames you refer to in your query must be declared prior to the `query_local`
# call.
my_df = pd.DataFrame(np.random.randn(6,4), columns=list('ABCD'))
result = data.sql.query_local("SELECT A, B FROM my_df")For added security, you may want to create a special, read-only user account to use with DataBlade:
CREATE ROLE datablade LOGIN PASSWORD 'databladepassword';
GRANT CONNECT ON DATABASE database TO datablade;
GRANT USAGE ON SCHEMA public TO datablade;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO datablade;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO datablade;- For your convenience, we allow you to upload files directly to DataBlade and use them in your projects as easily as you might use files locally on your computer. To upload, simply click the Upload button on the "Data Integrations" page
- In your code, access your files using the following snippets:
# Read a CSV file
my_csv = data.read_csv(FILE_NAME)
# Read a single-sheet Excel file (.xls or .xlsx) (or the first sheet of a workbook)
my_excel_file = data.read_excel(FILE_NAME)
# Read an entire Excel workbook
my_excel_file = data.read_excel(FILE_NAME, sheetname=None)
# Read a specific sheet from an Excel workbook
my_excel_file = data.read_excel(FILE_NAME, sheetname='Sheet Name') # May also be a numerical index
# Read a file as a StringIO buffer. Here is an example with line-delimited JSON.
my_file = data.read_file(FILE_NAME)
my_dataframe = [json.loads(line) for line in my_file]
# Write back to your user file store
# (creates a new file or overwrites existing)
# data.write_file(FILE_NAME, FILE_OBJECT)
# Example:
import StringIO
my_df = pd.DataFrame(np.random.randn(6,4), columns=list('ABCD'))
my_df_csv = StringIO.StringIO(my_df.to_csv(index=False))
data.write_file("random.csv", my_df_csv)- Under "Data Integrations", set up your S3 credentials
- Take note of the
IDvalue of your integration - In your code, access your files using the following snippets:
# For all: Replace INTEGRATION_ID, BUCKET_NAME, and KEY as appropriate
# Read a CSV file
my_csv = data.s3.read_csv(INTEGRATION_ID, BUCKET_NAME, KEY)
# Read a single-sheet Excel file (.xls or .xlsx) (or the first sheet of a workbook)
my_excel_file = data.s3.read_excel(INTEGRATION_ID, BUCKET_NAME, KEY)
# Read an entire Excel workbook
my_excel_file = data.s3.read_excel(INTEGRATION_ID, BUCKET_NAME, KEY, sheetname=None)
# Read a specific sheet from an Excel workbook
my_excel_file = data.s3.read_excel(INTEGRATION_ID, BUCKET_NAME, KEY, sheetname='Sheet Name') # May also be a numerical index
# Read a file as a StringIO buffer. Here is an example with line-delimited JSON.
my_file = data.s3.read_file(INTEGRATION_ID, BUCKET_NAME, KEY)
my_dataframe = [json.loads(line) for line in my_file]
# Write a File object to S3
data.s3.write_file(INTEGRATION_ID, BUCKET_NAME, KEY, FILE_OBJECT)- In DataBlade, set up a Google Analytics integration by providing a name for the integration and then clicking Authenticate with Google. Follow the prompts that appear to log in.
- In your code, query your Google Analytics data using the following snippets:
# See all available query parameters:
# https://developers.google.com/analytics/devguides/reporting/core/v3/reference?hl=en
# You do not need to pass in the `id` parameter, as DataBlade will automatically
# populate it for you.
results = data.ga.query(INTEGRATION_ID,
start_date='7daysAgo',
end_date='today',
dimensions="ga:browser",
metrics='ga:sessions')- Ensure that you create or select a project in the Google Developers Console and enable the BigQuery API
- If you already have a service account with a P12 key, you can skip this step. Otherwise, in the Developers Console for the chosen project, under APIs & auth, select Credentials. In the Add credentials dropdown, choose Service account. For Key type, select P12, then click Create. The private key should automatically download. Click Close in the dialog.
- In DataBlade, set up a BigQuery integration, providing the service email account, the generated
.p12file, and the associated project ID. - In your code, query your BigQuery data using the following snippets:
# Keep in mind that the format of BigQuery table names is <dataset name>.<table name>
results = data.bq.query(INTEGRATION_ID, SQL_QUERY)- If you haven't already done so, apply for the Google AdWords API
- Once API access is granted on your account, set up a Google AdWords integration in DataBlade, providing the Developer Token and Customer ID. Click the Authenticate with Google button to complete the authentication process. Make sure that the Google account with authenticate with has been granted AdWords permissions for the provided Customer ID. Then click Create.
- Reports for AdWords are defined using AWQL. You can see a full list of report types and queryable fields here.
- In your code, retrieve Google AdWords reports using the following snippets:
# Example AWQL query: "SELECT CampaignId, Clicks FROM CAMPAIGN_PERFORMANCE_REPORT DURING LAST_WEEK"
report = data.adwords.get_report(INTEGRATION_ID, AWQL_QUERY)- In DataBlade, set up a Google Sheets integration by providing a name for the integration and then clicking Authenticate with Google. Follow the prompts that appear to log in.
- In your code, query your Google Sheets data using the following snippets:
# The spreadsheet ID can be found in the URL of the spreadsheet
# Example: https://docs.google.com/spreadsheets/d/1AFtzJQxZ_Vhr2ikzwnBpstnf-tM9V-MMus9wT7LqXXX/edit
# The ID is located between /d/ and /edit, i.e. 1AFtzJQxZ_Vhr2ikzwnBpstnf-tM9V-MMus9wT7LqXXX
sheet = data.sheets.get_spreadsheet(INTEGRATION_ID, SPREADSHEET_ID)- Under "Data Integrations", set up your MongoDB integration. In Connection String, make sure to also include your database name in the URI.
- In your code, you can query your database using the following snippets:
results = data.mongodb.find(INTEGRATION_ID, COLLECTION_NAME, QUERY)
# You can also access the raw pymongo client
client = data.mongodb.get_client(INTEGRATION_ID)- Under "Data Integrations", set up your RethinkDB integration
- In your code, you can query your database using the following snippets:
import rethinkdb as r
# At the moment, you must execute all your commands within a with block
with data.rethinkdb.get_connection(INTEGRATION_ID) as conn:
result = r.table("mytable").run(conn)
# Alternatively, you can bypass the managed data integrations and use the driver directly
conn = r.connect(...)- Under "Data Integrations", set up your Salesforce integration by giving your integration a name, and then click Authenticate with Salesforce and follow the prompts that appear.
- You can now access your Salesforce objects using SOQL or SOSL using the following snippets:
# Learn more about SOQL here: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/
results = data.salesforce.query(INTEGRATION_ID, QUERY)NOTE: Your organization must be using an edition of Salesforce that has API access enabled. See here for more info.
- Create a new FTP integration with the host and the user credentials.
- In your code, you can access your FTP files using the following snippets:
# List the files in a directory
files = data.ftp.ls(INTEGRATION_ID, DIRECTORY)
# Get info about a specific file
file_info = data.ftp.get_info(INTEGRATION_ID, PATH)
# Get a File object (StringIO)
file_pointer = data.ftp.read_file(INTEGRATION_ID, PATH)- Create a new Facebook integration and authenticate with an account that has access to your Ad Accounts.
- In your code, you can access your campaign information using the following snippets:
# Get the Campaigns in your Ad Account
campaigns = data.fb.get_campaigns(INTEGRATION_ID, AD_ACCOUNT_ID)
# Get the Insights for a particular Campaign ID
insights = data.fb.get_campaign_insights(INTEGRATION_ID, CAMPAIGN_ID)
# Get Insights for all Campaigns in your Ad Account
all_insights = data.fb.get_all_campaign_insights(INTEGRATION_ID, AD_ACCOUNT_ID)
# You can also optionally pass in parameters, as documented here:
# https://developers.facebook.com/docs/marketing-api/reference/ad-campaign-group/insights/
# These parameters will work for both get_campaign_insights and get_all_campaign_insights
# For example:
insights_actions_only = data.fb.get_campaign_insights(INTEGRATION_ID, CAMPAIGN_ID, fields=["actions", "cost_per_action_type"])
insights_time_range = data.fb.get_campaign_insights(INTEGRATION_ID, CAMPAIGN_ID, time_range={"start": "2016-01-08", "until": "2016-01-10"})- Create a new Shopify integration by providing it a name and your shop. Click Authenticate with Shopify and follow the prompts that appear.
- In your code, you can access your Shopify store information using the following snippets:
# Get your Session and set up the Shopify Python SDK
import shopify
session = data.shopify.get_session(INTEGRATION_ID)
shopify.ShopifyResource.activate_session(session)
# You can now query your Shopify objects
# See additional documentation: http://shopify.github.io/shopify_python_api/
all_products = shopify.Product.find()
# Coming soon: APIs to load your Shopify objects directly into DataFramesIf you want to connect to other data sources that DataBlade doesn't support yet, please send us a data source addition request at [email protected]! We have a bunch of new data sources in our pipeline, but we're happy to prioritize what's important for you.
If you need to access an unsupported data source more quickly than we can get back to you and don't mind getting your hands dirty, you can try using pip install directly. Here's an example of installing the Stripe API using pip install:
import subprocess
subprocess.check_output(["pip", "install", "stripe"])
import stripe
You may find yourself in a situation where you’ve developed a workflow that you’d like other, potentially non-technical team members, to be able to leverage. We can easily do this with a Self-Service Dashboard.
Watch our video tutorial for creating Self-Service Dashboards here.
DataBlade provides a SDK for receiving parameter values from Self-Service Dashboard runs:
# For all functions, the first parameter is the key name
# (should be entered in "Parameter Keys" in the Dashboard config)
# The second parameter is the default value. You'll want to provide one in most cases.
# When you are developing in the IDE, there is no value provided for parameters, so
# the default value will always be used.
str_param = data.params.get_string("some_string_parameter", "default value")
int_param = data.params.get_int("some_int_parameter", 1)
float_param = data.params.get_float("some_float_parameter", 1.0)
bool_param = data.params.get_bool("some_bool_parameter", True)Since the URLs for Self-Service Dashboards are publicly available, we allow users to set a PIN code that must be entered before the report can be accessed. The PIN code can be configured in the Web tab in the Dashboard configuration pane. Leaving the PIN field empty will disable PIN protection.
API Keys can be created and managed from the Settings page. Note that you must be a part of an organization before creating an API key and that the keys you create are specific to an organization, which will limit what resources each API key will have access to.
Each project has a simple key-value store that can hold arbitrary JSON, making it very easy to store and retrieve state. The metadata store is also a handy place to store values that need to be accessed by an external tool, since metadata values are accessible via our API.
Keep in mind that the metadata store should not be used to store large amounts of raw data. For that, use your user file store or S3.
data.set_metadata(KEY, VALUE)
data.get_metadata(KEY, VALUE)Getting all keys & values
https://app.datablade.io/api/v1/projects/<PROJECT_ID>/metadata?access_token=<API_KEY>
Getting specific key
https://app.datablade.io/api/v1/projects/<PROJECT_ID>/metadata?access_token=<API_KEY>&key=<KEY>
DataBlade provides two functions for importing and running projects programmatically:
Import a project as a function:
data.import_project(project_id_or_name, params={},
return_modules=False, return_hidden=False,
show_output=True)Run a project directly:
data.run_project(project_id_or_name, params={},
return_modules=False, return_hidden=False,
show_output=True)The functions are very similar, with the one key difference:
import_projectreturns a function that, when called, will run the project. When the project is run, adictof any variables created by that project.run_projectjust runs the project immediately and returns the results
Here are some examples of how these can be useful:
You may have a DataBlade project containing functions you'd like to reuse in other DataBlade projects. You can do this using data.run_project. Take this project:
Project name: utils
def square(n):
return n * n
We can use data.run_project to use this project's square function in another project:
utils = data.run_project('utils')
result = utils.square(3)
You may have a DataBlade project that you'd like to run multiple times in slightly different ways. You can do this easily using data.import_project. Take this project:
Project name: facebook insights
fields = data.params.get_list('fields', default=['spend'])
insights = data.fb.get_all_campaign_insights('facebook', 'act_123456789', fields=fields)
We can use data.import_project to import this project as a function, then call it repeatedly with different parameters:
insights = data.import_project('facebook insights')
results1 = insights()
results2 = insights({'fields': ['spend', 'impressions']})
results3 = insights({'fields': ['cpm', 'cpc', 'spend', 'impressions', 'clicks']})
The example above uses a list parameter. Type data.params into the console so see the other types of parameters we support (e.g., int, string, etc).
While you can use project names with data.import_project and data.run_project, it's often better to use the project ID, for two reasons:
- The project ID is stable -- it won't change, even if the project is renamed
- Project IDs are unique -- if a project shares a name with another project, it cannot be imported by name
To get the project ID, open the project and look at the URL:
https://app.datablade.io/projects/57bc779e24a77c27ea8e0dd5
The ID for this project is everything after the last slash in the URL: 57bc779e24a77c27ea8e0dd5. Use this instead of the name parameter, for example:
results = data.run_project('57bc779e24a77c27ea8e0dd5')data.import_project and data.run_project both take three other keyword parameters:
- return_modules (default:
False): Include modules in results - return_hidden (default:
False): Include hidden variables in results - show_output (default:
True): Show output
We can use Python-fu to add a run/imported project's variables to the importing project's scope. This way, they'll be available directly with the variable picker and also callable directly. Extending the above utils example:
utils = data.run_project('utils')
globals().update(utils) # Here's the trick!
result = square(5)This is very similar to a Python's "from module import *"
- Run main code:
Ctrl-r(Windows),Cmd-r(Mac) - Outdent lines:
Ctrl-[(Windows),Cmd-[(Mac) - Indent lines:
Ctrl-](Windows),Cmd-](Mac) - Go to beginning of line:
Ctrl-a - Go to end of line:
Ctrl-e - Clear to beginning of line:
Ctrl-u - Clear to end of line:
Ctrl-k - Search:
Ctrl-f(Windows),Cmd-f(Mac) - Clear error and output popups:
Esc - Save (Auto-save works great, too!):
Ctrl-s(Windows),Cmd-s(Mac)
- Previous item in history:
Ctrl-p(Mac) - Next item in history:
Ctrl-n(Mac)
