Usage Workflow Through Query Logs

• Athena
• BigQuery
• Clickhouse
• Databricks
• Exasol
• MSSQL
• PostgreSQL
• Redshift
• Snowflake

Query Log File

• query_text: This field contains the literal query that has been executed in the database. It is quite possible that your query has commas , inside. Then, wrap each query in quotes to not have any clashes with the comma as a separator.
• user_name (optional): Enter the database user name which has executed this query.
• start_time (optional): Enter the query execution start time in YYYY-MM-DD HH:MM:SS.ffffff format. The fractional seconds are required.
• end_time (optional): Enter the query execution end time in YYYY-MM-DD HH:MM:SS format.
• aborted (optional): This field accepts values as true or false and indicates whether the query was aborted during execution
• database_name (optional): Enter the database name on which the query was executed.
• schema_name (optional): Enter the schema name to which the query is associated.

query_text,database_name,schema_name
"select * from sales",default,information_schema
"select * from marketing",default,information_schema
"insert into marketing select * from sales",default,information_schema

Redshift Serverless: Export SELECT Queries

1. Run the following query in your Serverless workgroup. Adjust the time window and filters for your environment:
   SELECT
       q.query_text,
       RTRIM(u.usename) AS user_name,
       TO_CHAR(q.start_time, 'YYYY-MM-DD HH24:MI:SS.US') AS start_time,
       q.end_time,
       q.database_name
   FROM SYS_QUERY_HISTORY AS q
       INNER JOIN pg_catalog.pg_user AS u
           ON q.user_id = u.usesysid
   WHERE q.query_type = 'SELECT'
       AND LOWER(q.status) = 'success'
       AND q.user_id > 1
       AND q.start_time >= DATEADD(day, -7, GETDATE())
   ORDER BY q.start_time DESC;
   
2. Save the result as a CSV file with a header row. The query_text column is required. The user_name, start_time, end_time, database_name, and schema_name columns are optional. Write start_time as YYYY-MM-DD HH:MM:SS.ffffff, which the export query does. If the format differs, the workflow stops reading the file and logs a warning. Quote values that contain commas or line breaks.
3. Set serviceName to your Redshift service and queryLogFilePath to this file in the YAML below, then run the workflow.

Usage Workflow

1. Create a configuration file using template YAML

source:
  type: query-log-usage
  serviceName: <name>
  sourceConfig:
    config:
      type: DatabaseUsage
      queryLogFilePath: <path to query log file>
processor:
  type: query-parser
  config: {}
stage:
  type: table-usage
  config:
    filename: "/tmp/query-usage"
bulkSink:
  type: metadata-usage
  config:
    filename: "/tmp/query-usage"
workflowConfig:
  openMetadataServerConfig:
    hostPort: <OpenMetadata host and port>
    authProvider: <OpenMetadata auth provider>

• queryLogFilePath: Enter the file path of query log csv file.

2. Run with the CLI

metadata usage -c <path-to-yaml>