Use Case: Retrieve Data With the Python Connector

In the Coveo Administration Console, the Snowflake reader account gives you direct access to your organization’s data and it enables you to explore that data as well as create customized exports and materialized views. You can use different Business Intelligence (BI) tools for data analysis, such as Tableau™ and Power BI™. Alternatively, you have the option to use a native Snowflake connector. In this article, we’ll demonstrate how to use Python™ to explore and export your data.

Step 1: Install the Connector

To get started, you must first install the Snowflake Connector for Python using the following command:

pip install snowflake-connector-python

For detailed installation steps, see Installing the Python Connector.

Step 2: Install Pandas

Once you’ve installed the connector, you must also install Pandas, which is a data analysis library that works with Python. Use the following command to install:

pip install pandas

For detailed installation steps, see Using Pandas DataFrames with the Python Connector.

Step 3: Connect to Snowflake

Following the installations of Snowflake Connector for Python and Pandas, you must create a script to connect to Snowflake, as shown in the following example:

import snowflake.connector
import pandas as pd

conn = snowflake.connector.connect(
   user='<xxxxxx>',
   password='<xxxxxxxx>',
   account='<account>',
   ocsp_fail_open=False,
)

Step 4: Define a SQL Query

Once you’re connected to Snowflake, you must define the SQL query, as shown in the following example:

sql_query = pd.read_sql_query('select * from "COVEO"."ua"."all_events_shared" where "datetime" between %(from_date)s and %(to_date)s'
,conn
,params={"from_date":from_date, "to_date": to_date})

Step 5: Execute Query and Add to DataFrame

Once defined, you can execute the query in the reader account’s SQL editor, as shown in the following example:

set from_date ='<FROM_DATE>';
set to_date ='<TO_DATE>';

select * from (
select date("datetime") as date, 'Searches' as event_type, count(*) from "ndev-coveoanalytics"."ua"."searches" where "datetime" between $from_date and $to_date group by date, event_type
union all
select date("datetime") as date, 'Clicks' as event_type, count(*) from "ndev-coveoanalytics"."ua"."clicks" where "datetime" between $from_date and $to_date group by date, event_type
union all
select date("datetime") as date, 'Custom Events' as event_type, count(*) from "ndev-coveoanalytics"."ua"."custom_events" where "datetime" between $from_date and $to_date group by date, event_type
union all
select date("datetime") as date, 'Views' as event_type, count(*) from "ndev-coveoanalytics"."ua"."views" where "datetime" between $from_date and $to_date group by date, event_type
  )
order by 1 asc

Afterwards, you must read the data into a Pandas DataFrame, using the following:

df = pd.DataFrame(sql_query)

Step 6: Export to CSV

Once you’ve executed the SQL query, export the data to a CSV file:

df.to_csv (r'searches.csv', index = False)

Reference

Code Sample

The script below is similar to the Python script used for UA data exports, however it’s shorter since it doesn’t require joining the various tables on the CSV file.

import snowflake.connector
import pandas as pd

conn = snowflake.connector.connect(
   user='xxxxxx',
   password='xxxxxxxx',
   account='account',
   ocsp_fail_open=False,
)

# No need to define org name, your reader account bound to your data
from_date = "2022-01-01"
to_date = "2022-01-02"

# Searches
sql_query = pd.read_sql_query('select * from "COVEO"."ua"."searches_shared" where "datetime" between %(from_date)s and %(to_date)s limit 10;'
                             ,conn
                             ,params={"from_date":from_date, "to_date": to_date})
df = pd.DataFrame(sql_query)
df.to_csv (r'searches.csv', index = False)

# Clicks
sql_query = pd.read_sql_query('select * from "COVEO"."ua"."clicks_shared" where "datetime" between %(from_date)s and %(to_date)s'
                             ,conn
                             ,params={"from_date":from_date, "to_date": to_date})
df = pd.DataFrame(sql_query)
df.to_csv (r'clicks.csv', index = False)

# Customs
sql_query = pd.read_sql_query('select * from "COVEO"."ua"."custom_events_shared" where "datetime" between %(from_date)s and %(to_date)s'
                             ,conn
                             ,params={"from_date":from_date, "to_date": to_date})
df = pd.DataFrame(sql_query)
df.to_csv (r'customs.csv', index = False)

# Views
sql_query = pd.read_sql_query('select * from "COVEO"."ua"."views_shared" where "datetime" between %(from_date)s and %(to_date)s'
                             ,conn
                             ,params={"from_date":from_date, "to_date": to_date})
df = pd.DataFrame(sql_query)
df.to_csv (r'views.csv', index = False)

# All events combined
sql_query = pd.read_sql_query('select * from "COVEO"."ua"."all_events_shared" where "datetime" between %(from_date)s and %(to_date)s'
                             ,conn
                             ,params={"from_date":from_date, "to_date": to_date})
df = pd.DataFrame(sql_query)
df.to_csv (r'all.csv', index = False)