Query gives you the ability to directly interact with your data using code in real time without leaving the Civis interface. You can write SQL queries against any of your Platform databases, and see the output displayed without the need for an external SQL client. Because all jobs take place in the cloud, queries will complete even if you close your browser or turn off your computer.

Parts of the Page

• Data Pane: Located along the left side, this adjustable navigation gives you the ability to view schemas, tables, column names, and more.  It also links to each table's full details page.
• Query Editor: The top of the page is the pane where you write and execute SQL queries. You can also limit the number of rows displayed in the preview of the results. 
• Query Results: The bottom of the page, below the Query Editor, will preview results of your executed query.
• Query History: When expanded from the right side, SQL code and results preview from your previous queries will show. By default, your most recent 10 queries will appear, but you can continue scrolling to show more queries.

Writing an SQL Query

You can execute a query by typing your SQL statement into the Query Editor and clicking the Run button on the left side of the editor or hitting Ctrl+Enter (Cmd + Enter on Mac) on your keyboard.  You have the ability to select the first 10, 20, 50, 100, or 1000 rows of your query results. Please note that an excessively long query may result in a timeout that is indicated by a rate limit warning.  To avoid this, you can either break your query into smaller pieces or use the SQL Script page.  

Using Autocomplete

As you type SQL, Query will suggest schemas, tables, and column names. A minimum of three characters or more is required for autocomplete suggestions to appear. This makes exploration faster and more accurate.  Hit tab or click with your mouse to choose one of the suggested options.

Cancel A Query

If you make a mistake when writing a query or find that your query is taking a long time to run, you can cancel it by clicking the cancel or trashcan icon next to the query within the Query History pane. You may also run separate queries simultaneously.

Multiple Statements

You can execute multiple statements separated by a ";" in a single query.  You can enter new lines in the Query Editor using Shift+Enter.

When multiple statements are provided in a single query, they are run in a single transaction, and the results from the last statement are returned.  

For most queries, an error anywhere in the query causes the whole query to have no effect. The notable exception to this is any query in which the final statement is a SELECT. In that case, the final SELECT is run separately from other statements in the query; thus, errors in a final SELECT statement will not cause rollbacks of other statements in the query. To avoid this behavior, you can use BEGIN and COMMIT SQL commands within your query.  See this article for more information.  

Partial Runs

You can run a partial query by highlighting a specific section of the SQL before running the query; the query tool will only run the selected section rather than the entire query.

Query Actions

After a query successfully completes, you are presented with action buttons above the query editor that will allow you to:

• Copy the query text to your clipboard.
• Create a newly saved script from your query so that you can integrate it into a workflow.

You can take other actions as well with the action buttons located on the top right above the query results area. These actions include:

• Copy the preview result set to your clipboard.
• Export the full result set as a CSV.

Viewing Your Query Results

In the query results area, you can resize the column widths by dragging the right portion of the column separator. 

You can also reorder the columns by dragging the header cells and moving them left or right.

 You may alter the values in the query results area by clicking the ellipsis menu in the column header of your choosing. You may take any of the following actions:

• Unsort the order of the rows (this will be grayed out until you sort the order)
• Sort the rows in ascending order
• Sort the rows in descending order
• Filter the columns by value
• Hide the column
• Show all columns, search for a column name, and toggle the visibility of that column to be off or on
• Pin a column to the left to continue seeing it as you scroll
• Pin a column to the right to continue seeing it as you scroll

Query History

The query history pane can be opened from the top right corner.  Once opened, you can search and filter previously executed queries. The actions you can take are:

• Perform full-text search on SQL query content by entering in a minimum of 2 characters
• Filter by the timeframe of when your query was executed (last 7 days, 14 days, 30 days, or 90 days)
• Filter query history by execution status (successful or all statuses)

Each previously executed query is presented in a query card with the SQL query, when it was executed, and results if any. By clicking ‘View Results’ you will see results from that query at the point in time it ran. This does not show up to date results if data has changed. Results are available for 30 days after query execution.

Once ‘View Results’ is expanded, you can copy the preview results to the clipboard. Additionally, hovering over the query card will provide a handful of shortcut actions for you to take:

• Run the query again allowing you to quickly re-execute the query in a single click
• Copy your code to the query editor will paste the SQL query into the editor
• Create a new saved script from your query so that you can integrate it into a workflow
• Delete the query card from your query history so it will no longer be visible