Skip to main content

Enable the Query Audit Log

A query audit log for the Ahana-managed Presto cluster is collected and stored in an AWS S3 bucket. If access to the query audit log was not enabled when the Presto cluster was created, the table mapping can be added manually.

note

Use the Presto CLI or a similar application to connect to the Ahana-managed Presto cluster and run queries and commands.

To enable the query audit log for an existing Ahana-managed Presto cluster:

  1. In the Ahana SaaS Console, select Data Sources, then Buckets.

  2. In the Ahana-managed S3 Buckets table, find the S3 Bucket Name in the Presto cluster that contains -ql-, for example ahana-name-clustername-ql-5mekd18548. This S3 bucket name is needed to create the query audit log table.

  3. Connect to the Presto cluster using the Presto CLI or a similar application.

  4. To create a schema, run the following command:

CREATE SCHEMA catalog.schema

  • Replace catalog and schema with the names of the catalog and the new schema. For example, ahana_hive and querylog.
  1. To create the table, run the following command:

CREATE TABLE catalog.schema.query-table-name ( record varchar, dt varchar ) WITH ( external_location = 's3a://querylog-bucket', format = 'TEXTFILE', partitioned_by = ARRAY['dt'] )

  • Replace catalog and schema with the names of the catalog and the schema.
  • Replace query-table-name with the name of the table.
  • Replace querylog-bucket with the S3 Bucket Name identified in step 2.
  1. To create a view to use with the query audit log, run the following command:

CREATE VIEW catalog.schema.query-view-name AS SELECT "json_extract_scalar"(record, '$.clusterName') cluster_name , "json_extract_scalar"(record, '$.queryCompletedEvent.metadata.queryId') query_id , "json_extract_scalar"(record, '$.queryCompletedEvent.metadata.queryState') query_state , "from_unixtime"(CAST("json_extract_scalar"(record, '$.queryCompletedEvent.createTime.epochSecond') AS bigint)) create_time , "json_extract_scalar"(record, '$.queryCompletedEvent.context.user') user , "json_extract_scalar"(record, '$.queryCompletedEvent.metadata.query') query , "json_extract_scalar"(record, '$.queryCompletedEvent.statistics.totalRows') totalRows , "json_extract_scalar"(record, '$.queryCompletedEvent.statistics.outputRows') outputRows , "json_extract_scalar"(record, '$.queryCompletedEvent.statistics.writtenOutputRows') writtenOutputRows , "json_extract_scalar"(record, '$.queryCompletedEvent.statistics.totalBytes') totalBytes , "json_extract_scalar"(record, '$.queryCompletedEvent.statistics.outputBytes') outputBytes , "json_extract_scalar"(record, '$.queryCompletedEvent.statistics.cumulativeMemory') cumulativeMemory , "json_extract_scalar"(record, '$.queryCompletedEvent.statistics.completedSplits') completedSplits FROM catalog.schema.query-table-name WHERE ("json_extract"(record, '$.queryCompletedEvent') <> JSON 'null')

  • Replace catalog and schema with the catalog and schema that the table was created in.
  • Replace query-view-name with the name of the view.
  • Replace query-table-name with the name of the table.
  1. To see the content of the created view, run the following command:

SHOW CREATE VIEW catalog.schema.query-view-name

  • Replace catalog and schema with the catalog and schema that the table was created in.
  • Replace query-view-name with the name of the view.
  1. To sync the metastore with the table, run the following command:

call system.sync_partition_metadata('schema', 'query-table-name', 'FULL')

  • Replace schema with the name of the schema that the table was created in.
  • Replace query-table-name with the name of the table.