Collections
Query Collections are sets of SQL queries imported into the Single Origin app. Collections are a convenient place for you to see the queries you are working with and perform actions like running a query audit or parsing queries into Single Origin's entity model.
Navigate to the Query Collections page by selecting Manage in the navigation bar and then clicking Collections. From here, administrators can create new query collections and view a history of previously created query collections. Click on a query collection to see more details about that collection.
Prerequisites for a new Query Collection
- Queries must come from a valid source that can be imported into Single Origin
- Queries must include:
- A compiled, executed query
- The executor of the query
- A list of tags to associate with the query. Tags can either be a single item, like an Airflow task ID, or multiple items.
Note
Your personal connector must have permission to access all referenced tables.
Valid Sources
Queries may come from 1 of 3 sources:
- A CSV file
- A Compliant Query Table
- A Query History Table
CSV file upload
Each CSV file must follow the below format, with each column separated by a comma. If a header is present, use the column names below (query, user, and tags).
| query | user | tags |
|---|---|---|
| STRING - a SQL query to include in the audit. Include "at the start and end of the query. | STRING - the email of the "owner" of the query or the email of who "ran" the query | STRING (comma separated) - tags of metadata to associate with the query. tags can be a single item (like a task ID or team that owns the pipeline) or multiple items. Include " at the start and end of the list, and (if possible) do not just use numbers as tags. For example, "10" should ideally be "query_10" (for import purposes). |
An example CSV looks like this:
| query | user | tags |
|---|---|---|
| select lines.L_SHIPDATE as ship_date, parts.P_BRAND as part_brand, AVG(lines.L_DISCOUNT) as avg_discount from sandbox-demo-db.tpch_sf1.lineitem lines join sandbox-demo-db.tpch_sf1.part parts on lines.L_PARTKEY = parts.P_PARTKEY group by lines.L_SHIPDATE, parts.P_BRAND | [email protected] | tpch_sf1, customer_orders_by_day |
Compliant Query Table
A Compliant Query Table is similar in structure to a CSV file but must include a column representing the time the query was executed:
| start_time | query | user | tags |
|---|---|---|---|
| TIMESTAMP - the time the query was executed | STRING - a SQL query to include in the audit | STRING - the email of the "owner" of the query or the email of who "ran" the query | STRING (comma separated) - tags of metadata to associate with the query. tags can be a single item (like a task ID or team that owns the pipeline) or multiple items |
An example Compliant Query Table looks like this:
| start_time | query | user | tags |
|---|---|---|---|
| 2022-06-18 20:39:14.668604 UTC | select lines.L_SHIPDATE as ship_date, parts.P_BRAND as part_brand, AVG(lines.L_DISCOUNT) as avg_discount from sandbox-demo-db.tpch_sf1.lineitem lines join sandbox-demo-db.tpch_sf1.part parts on lines.L_PARTKEY = parts.P_PARTKEY group by lines.L_SHIPDATE, parts.P_BRAND | [email protected] | tpch_sf1, customer_orders_by_day |
Query History Table
Query History Tables are exported from Data Warehouse providers and provide a history of query executions in a standardized format. Fill out the form with the following fields (note that not all fields are available for all warehouses):
| Form Input | Options | Required? | Example Use Case |
|---|---|---|---|
| Name | Name of the collection | Yes | |
| Date and Time Range | The queries in the collection were run between this start and end time (in UTC). Time can be specified at the minute level. | Yes | |
| Limit | The maximum number of queries to add to the collection. | Yes | |
| Min/Max Row Number Count | The queries in the collection resulted in this many rows when run. | No | Bring in queries that generated ten rows of data or more. Queries that output 0 or 1 row may not be broadly reusable. |
| Emails | The queries in the collection were run by users associated with this set of emails (can be more than one email). | No | Bring in queries for either you or your team |
| Referencing Table | The queries in the collection all reference this table. | No | Create a collection that only contains queries that reference key_table (either by itself or with other tables) |
| Advanced Filter | 1. INTERACTIVE (queries run interactively) 2. BATCH (queries run as part of a batch process) 3. HIGH (queries run at a high priority level). | No | |
| Only include queries with write operations | The queries in the collection either created or inserted into a table (or not). | No | Bring in queries that are part of your pipelines so that you can audit them. Alternatively, bring in ad hoc queries (that are not part of your pipelines) so that you can audit them. |
Note
Using Query History Table as a source assumes that you are exporting your logs in a standardized way. For example:
- in BigQuery, the standard format is a table called
<project_id>.auditlog_dataset.cloudaudit_googleapis_com_data_access_using a process like this. Tables with different formats will not appear in the tables list.- in Snowflake, the standard format is a table callsed
SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
Details
Once you have created a collection, you can select it from the table on the Manage Queries page. You will then see more details about queries in the collection, including:
- Index: an integer to identify the query in the collection
- Query run by: the user that ran the query
- Runtime: when the query was executed
- Tags
- The full SQL
When you no longer need a collection, then you can delete it.
Deleting a Collection
When you delete a collection, you delete its associated audits and imports.
Query Audit
Once you have created a collection, you can run a Query Audit on the queries in the collection. To start a Query Audit, click the "New Query Audit" button at the top right corner of the Audits tab on the Manage Queries page: https://{tenant}.singleorigin.tech/manage?tab=audits.
Batch Import
Once you have created a collection, you can have the queries in the collection parsed into entities using Single Origin's common definition model. To start a Batch Import of SQL queries in a collection, click the "New Import" button at the top right corner of the Imports tab on the Manage Queries page: https://{tenant}.singleorigin.tech/manage?tab=imports.
FAQ
- When creating a new query collection, are any validations run on the queries provided?
- No. When you run an audit or import of the collection, that is when queries are parsed and validated.
- Is there a max size of a collection?
- The max size for a single collection is 10 million queries.
- Is there a max number of collections I can create?
- Yes, 100.
- What role is needed to create a collection?
- A user must be an admin to create a collection.
- Can I edit queries in a collection?
- Not yet, but this is coming soon!
- Can I search queries in a collection?
- Not yet, but this is coming soon!
Updated 6 days ago