Documentation

Join Different Data Sources

Before you start running queries across multiple data sets in datapine you will need to join data sources by defining relationships between your individual data sources. Common fields used to combine two data sets are unique IDs that help you to link the information in one table to the data in another.

 

Please follow the steps below to learn how to join multiple data sources using IDs or other unique fields in datapine.

 

Requirements

 

  • To run cross-database queries your databases should be stored in our data warehouse. Please see Switch you storage location to learn how to transfer your database into our data warehouse.
  • At least one of the two fields you are using to join your data sources should contain only unique values. 

Join a CSV file with another data source

 

Go to Settings and select the data source you wish to connect to another table or file. Clicking on Edit in the right lower corner of the data source widget will redirect you to the Data Source Screen.

 

Click on the Link ‘Connect this CSV’s data to that of other datasources’ on top of the data sources screen. Follow the steps below to link your CSV to any other data source in your datapine account.

 

join-data-source-example

 

  1. Select the field that shall be connected to the field of another data source from the CSV files data structure on the left.
  2. From the dropdown menu on the right select the data source you wish to connect to your CSV file.
  3. Choose the field that corresponds to the field in your selected CSV file.
  4. Click on Add to create the relation between the two highlighted fields.
  5. Go back to Data Sources to save the new relationships. You are now ready to perform cross-database queries on the fly.

Note: You cannot connect your data sources to other APIs such as Google Analytics or Facebook. If you want to run cross-database queries on your GA or Facebook data please contact us at support@datapine.com to discuss how we can help you to make that work.

 

Join multiple databases

 

Before you are ready to use our cross-database querying function, you will need to previously join your databases using Foreign Keys. Please keep in mind that this option is only available for databases stored in our data warehouse.

 

Go to Settings and access the database you wish to connect to another source with a click on the Edit icon in the bottom right corner of the database widget.

 

  1. In the Database Connection Screen click on References in the upper navigation bar to access the list of Foreign Keys.
  2. To setup a new Foreign Key Relation, select the table and field you wish to connect to another database from the left database structure.
  3. From the dropdown menu on the right, choose the data source you wish to connect to your database.
  4. Select the table and the field that corresponds to the field you’ve selected in Step 2.
  5. Add this new connection to your List of Foreign Keys with a click on Add.
  6. Click on Analyze in the upper tool navigation to start running your cross-database queries.