On this page

Amplitude SQL: Getting started with Query

The Amplitude Query product lets you query your raw data through your Amplitude-managed Snowflake database. Query also includes a chart type called Amplitude SQL that lets you write custom SQL against your Amplitude data directly inside the Amplitude platform.

Amplitude loads data into Snowflake every 30 minutes.

This feature isn't available in the EU.

Getting started

Find Amplitude SQL through Create > Chart > View additional chart types. You can save it, share it, and add it to a dashboard like any other chart.

This feature doesn't support queuing data for Portfolio Views.

You can also connect directly to your Snowflake database through a terminal or third-party application like SQL Workbench or the Snowflake connector for Python. Contact Amplitude Support or your Success Manager for your Snowflake credentials.

Set up and syntax

The Query package uses a simplified table schema for Snowflake and Amplitude SQL. The schema includes a single table you can reference with the shorthand $events.

To access other tables, use the full name, which you can find under Show Schema.

Query tables support unlimited data fields. Query stores custom user properties and event properties as variants, which you can query as individual columns. Custom user properties have the prefix user_properties:, and event properties have the prefix event_properties:.

If your user or event properties contain a period or a space, wrap the property name in quotes: user_properties:"first name".

If you're searching for a certain value, wrap the property value in single quotes: user_properties:"plan type"='enterprise'.

By default, Amplitude SQL shows a simple SQL query for events your users performed in the past 30 days. The SQL syntax includes the following fields:

  • $date as Date (the date of the events).
  • COUNT(DISTINCT $amplitude_id) with the alias Uniques (count of unique users).
  • COUNT($amplitude_id) as Totals (total count of users).

The SQL syntax highlighting helps you distinguish SQL commands from the rest of your query.

Amplitude SQL also supports autocomplete of columns in the table.

Query results

After you finish building your SQL query, click Compute to run it. When your query finishes running:

  • Query results appear in a data table.
  • A time series chart of the results appears below the table.

Use the controls below the query editor to customize the time series visualization. Your options include all the fields you return in your SQL SELECT statement. For example, in the query above, these are DATE, UNIQUES, and TOTALS.

  • X-axis column: Select what to use for the X-axis. This must be a time series.
  • Metric column: Select a field returned by your SQL SELECT statement to plot on the Y-axis.

Applying group-bys

To group the chart by a column, enter the column name in the Label columns field, then click Compute.

Sharing and saving queries

To export the results as a PNG, PDF, or CSV file, go to More > Export. You can also save your analysis and share it with your team, or add the visualizations you create to a dashboard in Amplitude.

Data table results and .CSV exports have a limit of 1,000 rows.

Next, learn about special field shortcuts to query your Snowflake data more quickly.

Was this helpful?