Connect RStudio to Query Service
This document walks through the steps for connecting RStudio with Adobe Experience Platform Query Service.
This guide assumes you already have access to RStudio and are familiar with how to use it. More information about RStudio can be found in the official RStudio documentation.
Additionally, to use RStudio with Query Service, you need to install the PostgreSQL JDBC 4.2 Driver. You can download the JDBC Driver from the PostgreSQL official site.
Create a Query Service connection in the RStudio interface
After installing RStudio, you need to install the RJDBC package. Go to the Packages pane, and select Install.
A pop up appears, showing the Install Packages screen. Ensure that Repository (CRAN) is selected for the Install from section. The value for Packages should be RJDBC. Ensure Install dependencies is selected. After confirming all the values are correct, select Install to install the packages.
Now that the RJDBC package has been installed, restart RStudio to complete the installation process.
After RStudio has restarted, you can now connect to Query Service. Select the RJDBC package in the Packages pane, and enter the following command in the console:
pgsql <- JDBC("org.postgresql.Driver", "{PATH TO THE POSTGRESQL JDBC JAR}", "`")
Where {PATH TO THE POSTGRESQL JDBC JAR} represents the path to the PostgreSQL JDBC JAR that was installed on your computer.
Now, you can create your connection to Query Service by entering the following command in the console:
qsconnection <- dbConnect(pgsql, "jdbc:postgresql://{HOSTNAME}:{PORT}/{DATABASE_NAME}?user={USERNAME}&password={PASSWORD}&sslmode=require")
For more information on finding your database name, host, port, and login credentials, visit the credentials page on Platform. To find your credentials, log in to Platform, then select Queries, followed by Credentials.
Writing queries
Now that you have connected to Query Service, you can write queries to execute and edit SQL statements. For example, you can use dbGetQuery(con, sql) to execute queries, where sql is the SQL query you want to run.
The following query uses a dataset containing Experience Events and creates a histogram of page views of a website, given the device’s screen height.
df_pageviews <- dbGetQuery(con,
"SELECT t.range AS buckets,
Count(*) AS pageviews
FROM (SELECT CASE
WHEN device.screenheight BETWEEN 0 AND 99 THEN '0 - 99'
WHEN device.screenheight BETWEEN 100 AND 199 THEN '100-199'
WHEN device.screenheight BETWEEN 200 AND 299 THEN '200-299'
WHEN device.screenheight BETWEEN 300 AND 399 THEN '300-399'
WHEN device.screenheight BETWEEN 400 AND 499 THEN '400-499'
WHEN device.screenheight BETWEEN 500 AND 599 THEN '500-599'
ELSE '600-699'
end AS range
FROM aa_post_vals_3) t
GROUP BY t.range
ORDER BY buckets
LIMIT 1000000")
A successful response returns the results of the query:
df_pageviews
buckets pageviews
1 0 - 99 198985
2 500-599 67138
3 300-399 2147
4 200-299 354
5 400-499 6947
6 100-199 4415
7 600-699 3097040
Next steps
For more information on how to write and run queries, please read the guide on running queries.