Linked Data Sources

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:

  • MySQL
  • Microsoft SQL server
  • PostgreSQL
  • 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.

 

Connecting

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)
    • Port
    • User name
    • Password
    • 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:

  • 52.2.68.228
  • 52.44.248.121

Notice that from September 1st, 2018 the following IPs must be white listed instead of the above mentioned IPs:

  • 35.199.16.189
  • 35.188.238.92

Google BigQuery and Google Cloud Storage require you to give the Cooladata services access to the Google Project – contact us at support@cooladata.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:

FROM {connection_type}.{connection_name}.{table_name}
  • 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

Example:

FROM mysql.mydb.users

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.

 

Print Friendly, PDF & Email

JDBC

CoolaData JDBC driver allows you to query your data on CoolaData using external applications. It can be integrated with any visualization tool to run over the data stored in CoolaData.
Installation varies between applications. Download the latest version of the driver here:  https://github.com/cooladata/JDBC

The connection URL in all apps for Cooladata’s JDBC is:

jdbc:gdata:sql//app.cooladata.com/v2/{PROJECT_ID}?{params}

Each connection is for a specific project. To connect to multiple projects create another connection with another project_id in the URL.

Special URL parameters

For special functions you can append the following params to the connection URL:

  • Escape Quotes: If your query engine adds double quotes to sql queries, add escapeQuotes=true parameter to connection URL.  This flag deletes all double quotes from SQL queries since CoolaData server works with table and column names without double quotes. For example:
    jdbc:gdata:sql//{SERVER_URL}/v2/{PROJECT_ID}?escapeQuotes=true
  • Date format: If dates in query results should be presented in GMT timezone (UTC time), add gmtTimezone=true parameter to connection URL. This flag changes default timezone to GMT. Please note: restart for query engine is required after removing gmtTimezone parameter from JDBC URL. For example:
    jdbc:gdata:sql//{SERVER_URL}/v2/{PROJECT_ID}?gmtTimezone=true
  • Log file and debug level: If you are asked to get a log of what exactly the driver execute and process, you can specify a level of debug and a log file that all debug data will be written to it. add loglevel=debug&logfile=[file path] parameters to connection URL. Please note: using the log file will slow down performance of the driver as it writes a lot of text. For example:
    jdbc:gdata:sql//app.cooladata.com/v2/123456?loglevel=debug&logfile=c:\temp\coola-jdbc-log.txt

Standard SQL

Cooladata support both sql dialects of BigQuery, Legacy and Standard. By default JDBC uses Legacy format.
To run standard sql in JDBC just add at the beginning of SQL the string #STANDARDSQL .
For example:

#STANDARDSQL
select event_time_ts from cooladata where date_range(last 90 days)

Dbeaver Setup

To set up CoolaData’s JDBC driver on Dbeaver please follow these steps:

  1. Open Dbeaver
  2. From the top menu choose: Database>Driver Manager
  3. Click “New”
    1
  4. Give the new driver a name, such as: “CoolaData”
  5. Click “Add File”, locate and select the driver you downloaded
    2
  6. Click “Find Class” and choose the result found
    3
  7. Enter the following URL Template: jdbc:gdata:sql//app.cooladata.com/v2/{PROJECT_ID}?{params}
  8. Click “OK”
  9. 5
  10. Close the driver manager
  11. From the menu choose: Database>New Connection
    6
  12. Select the newly created connection (“CoolaData”)
  13. Click “Next”
    7
  14. Enter your CoolaData username (email) and password
  15. Click “Next”, “Next”, “Finish”
  16. From the menu choose: SQL Editor>SQL Editor
  17. Write your query and hit CTRL+Enter to run it

 

SQL WorkBench J

  1. Make sure you have Java installed on your desktop: Java SE Runtime Environment can be downloaded from: http://www.oracle.com/technetwork/java/javase/downloads/index.html
  2. Open SQL Workbench J
  3. Set up a new driver: open the “Manage Drivers” menu and create a new entry; name it “CoolaData”, and select the file you downloaded from your computer; click OK to save an exit.
    Manage drivers
  4. From the “Connect window” (Alt + C), add a new connection profile.
  5. Name the new profile.
  6. Enter the following URL (see above for connection parameters): jdbc:gdata:sql//app.cooladata.com/v2/{PROJECT_ID}?{params}
  7. From the “Driver” drop down choose “CoolaData (com.cooladata.driver.CoolaDataDriver)”.
  8. Enter your CoolaData username and password.
  9. Click OK.

sql j

 

R

Run the following commands in R Studio:

install.packages("RJDBC",dep=TRUE)
library(RJDBC)
drv <- JDBC("com.cooladata.driver.CoolaDataDriver", "Path to the JDBC file/jxDBCon-jdbc3-0.9.9.jar", identifier.quote="`")
conn <- dbConnect(drv, "jdbc:gdata:sql//app.cooladata.com/v2/projectid", "username","password")
dbGetQuery(conn, "<query>")
Print Friendly, PDF & Email

Metric Insights

Metric Insights allows you to enjoy the power of personal alerts according to pre-defined rules, based on your data stored in CoolaData.

Configuring Metric Insights

In the Metric Insight dashboard, create a new data source. In the wizard, go to Admin → More → Data → Data Sources → Add New Data Source → Select Plug-In: CoolaData. Enter the name of your data source, and save.

MetricInsight1
To set parameters for the plug-in, go to Plug-in Configuration → Parameters. Enter the project ID in the Project field, and then go to Admin Console → Projects → Select Project, and copy the last segment in the URL in the admin console. The token is the user token from the admin console.
MetricInsight2
Next, create a new element, and then configure your metrics as needed.

For more information, see the Metric Insights online help.

Print Friendly, PDF & Email

Slack

Slack brings all your team communication into one place.  With email apps, you can skip your inbox and receive publication and alert emails directly in Slack.

Here’s how it works:

  1. See Send emails to Slack to set up an email app. You’ll get a special email address that delivers to the Slack channel of your choice.
  2. Enter this email in any publication, alert, or notification emails list to be notified directly to your Slack channel.
  3. Emails will show up as a message in the Slack channel or DM of your choice, together with the attached files.

 

Print Friendly, PDF & Email

MailChimp

MailChimp enables you to engage your leads with great email marketing campaigns. Connecting Cooladata to your MailChimp account will allow you to link these campaigns with any other data source, to capitalize on the leads that matter.

Each day, the integration fetches the previous day’s aggregative data and appends a row to the table. For example, on Aug 4th a row would be added showing aggregated data up until Aug 3rd, such as how many emails were sent from the beginning of the campaign. See the full table scheme below.

To integrate MailChimp to your Cooladata project please contact your customer success manager or email us at support@cooladata.com.

 

Table Name: mailchimp_campaigns

FieldData TypeDescription
campaign_idSTRINGA string that uniquely identifies this campaign.
click_rateFLOATThe number of unique clicks divided by the total number of successful deliveries.
clicksINTEGERThe total number of clicks for an campaign.
creation_dateTIMESTAMPThe date and time the campaign was created.
drag_and_dropSTRINGWhether the campaign uses the drag-and-drop editor.
from_nameSTRINGThe ‘from’ name on the campaign (not an email address).
hard_bouncesINTEGERThe total number of hard bounced email addresses.
insert_dateTIMESTAMPThe date this row was inserted to the table
list_idSTRINGThe unique list id.
list_nameSTRINGThe name of the list.
open_rateFLOATThe number of unique opens divided by the total number of successful deliveries.
opensINTEGERThe total number of opens for a campaign.
send_dateTIMESTAMPThe date and time a campaign was sent.
sentINTEGERThe total number of emails sent for this campaign.
soft_bouncesINTEGERThe total number of soft bounced email addresses.
statusSTRINGThe current status of the campaign.Possible Values: save paused schedule sending sent
subject_lineSTRINGThe subject line for the campaign.
subscriber_clicksINTEGERThe number of unique clicks.
titleSTRINGThe title of the campaign.
unique_clicksINTEGERThe total number of unique clicks for links across a campaign.
unique_opensINTEGERThe number of unique opens.
unsubscribedINTEGERThe total number of unsubscribed members for this campaign.
Print Friendly, PDF & Email