CoolaData provides the capability to query data from linked (external) sources, providing you with a unified view of all your data in one place. This allows you to:
- Query, visualize and share data from linked data sources using the CQL report and dashboards.
- Join data from CoolaData and linked data sources using the fusion report.
- Generate Aggregation Tables over linked data sources, and save them to a linked data sources (Google Bucket).
You can connect your Cooladata project to the following databases:
- Microsoft SQL server
- Oracle Database
- Amazon Redshift
- Google BigQuery
- Google Cloud Storage – used as a destination for Aggregation Tables results (saved as CSV files in the bucket). Does not support queries.
To connect to an external database, add a new linked data source and configure the connection as explained on screen. Each database has its own connection parameters requirements.
- Connection Name: used for all connections – this is the name you will use to query this connection in CQL queries (see Querying a Linked Data Source below).
- MySQL, Microsoft SQL server, PostgreSQL, Oracle Database, Amazon Redshift will also require:
- Database name
- Host (or URL for Microsoft SQL server)
- User name
- SSL (on/off)
- Google BigQuery requires the Google Project ID and Dataset, and the BigQuery Syntax to be used in queries from this connection (Legacy SQL or Standard SQL).
- Google Cloud Storage only requires the Google Bucket name.
Authorizing Cooladata on your database
The following IPs must be opened in your firewall in order to use MySQL, Microsoft SQL server, PostgreSQL, Oracle Database or Amazon Redshift connections:
Notice that from September 1st, 2018 the following IPs must be white listed instead of the above mentioned IPs:
Google BigQuery and Google Cloud Storage require you to give the Cooladata services access to the Google Project – contact us at email@example.com for details.
Querying a Linked Data Source
You can query the linked data source using a CQL query. Make sure to use the project where you configured the linked data source. Specify the table names in the FROM clause like so:
- connection_type: you can use the specific connection type expressions: “mysql”, “mssql”, “bigquery”, “redshift”, “postgresql”, or the general term “external” for any Linked Data Source connection type.
- connection_name: the name you chose for the Linked Data Source
- table_name: the name of the table in your database
Note that you can also drag&drop the specific table into your query from the Schema (on the left) to automatically insert it with the correct syntax.
To use the date picker with queries on Linked Data Sources, use the date_range_mysql or date_range_gcp functions – see Date and Time Functions for more information.
Importing data from a Linked Data Source
In order to join the data from your linked data source and Cooladata, you will need to import data to tables in your Cooladata project. This is done using the Aggregation Tables function.
Just create a new aggregation table and query your Linked Data Source. The aggregation table will save the data fetched in the query to a new table in your Cooladata project, which will now be available for Joining with your Cooladata data.