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

Product Limitations

When you are using "SELECT * from …" , the database returns only the first 1,000 records. If you would like to return a larger number of records, you must specify the columns names in the SELECT statement.

Standard SQL

Journey Analytics 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 Journey Analytics's JDBC driver on Dbeaver please follow these steps:

1. Open Dbeaver
2. From the top menu choose: Database>Driver Manager
3. Click "New"

   

4. Give the new driver a name, such as: "CoolaData"
5. Click "Add File", locate and select the driver you downloaded

   

6. Click "Find Class" and choose the result found

   

7. Enter the following URL Template:

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

8. Click "OK"

   

9. Close the driver manager
10. From the menu choose: Database>New Connection

    

11. Select the newly created connection ("CoolaData")
12. Click "Next"

    

13. Enter your Journey Analytics username (email) and password
14. Click "Next", "Next", "Finish"
15. From the menu choose: SQL Editor>SQL Editor
16. 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.

   

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 Journey Analytics username and password.
9. Click OK.

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>")