system.query_views_log

Contains information about the dependent views executed when running a query, for example, the view type or the execution time.

To start logging:

  1. Configure parameters in the query_views_log section.
  2. Set log_query_views to 1.

The flushing period of data is set in flush_interval_milliseconds parameter of the query_views_log server settings section. To force flushing, use the SYSTEM FLUSH LOGS query.

ClickHouse does not delete data from the table automatically. See Introduction for more details.

You can use the log_queries_probability setting to reduce the number of queries, registered in the query_views_log table.

Columns:

  • event_date (Date) — The date when the last event of the view happened.
  • event_time (DateTime) — The date and time when the view finished execution.
  • event_time_microseconds (DateTime) — The date and time when the view finished execution with microseconds precision.
  • view_duration_ms (UInt64) — Duration of view execution (sum of its stages) in milliseconds.
  • initial_query_id (String) — ID of the initial query (for distributed query execution).
  • view_name (String) — Name of the view.
  • view_uuid (UUID) — UUID of the view.
  • view_type (Enum8) — Type of the view. Values:
  • view_query (String) — The query executed by the view.
  • view_target (String) — The name of the view target table.
  • read_rows (UInt64) — Number of read rows.
  • read_bytes (UInt64) — Number of read bytes.
  • written_rows (UInt64) — Number of written rows.
  • written_bytes (UInt64) — Number of written bytes.
  • peak_memory_usage (Int64) — The maximum difference between the amount of allocated and freed memory in context of this view.
  • ProfileEvents (Map(String, UInt64)) — ProfileEvents that measure different metrics. The description of them could be found in the table system.events.
  • status (Enum8) — Status of the view. Values:
    • 'QueryStart' = 1 — Successful start the view execution. Should not appear.
    • 'QueryFinish' = 2 — Successful end of the view execution.
    • 'ExceptionBeforeStart' = 3 — Exception before the start of the view execution.
    • 'ExceptionWhileProcessing' = 4 — Exception during the view execution.
  • exception_code (Int32) — Code of an exception.
  • exception (String) — Exception message.
  • stack_trace (String) — Stack trace. An empty string, if the query was completed successfully.

Example

Query:

  1. SELECT * FROM system.query_views_log LIMIT 1 \G;

Result:

  1. Row 1:
  2. ──────
  3. event_date:              2021-06-22
  4. event_time:              2021-06-22 13:23:07
  5. event_time_microseconds: 2021-06-22 13:23:07.738221
  6. view_duration_ms:        0
  7. initial_query_id:        c3a1ac02-9cad-479b-af54-9e9c0a7afd70
  8. view_name:               default.matview_inner
  9. view_uuid:               00000000-0000-0000-0000-000000000000
  10. view_type:               Materialized
  11. view_query:              SELECT * FROM default.table_b
  12. view_target:             default.`.inner.matview_inner`
  13. read_rows:               4
  14. read_bytes:              64
  15. written_rows:            2
  16. written_bytes:           32
  17. peak_memory_usage:       4196188
  18. ProfileEvents:           {'FileOpen':2,'WriteBufferFromFileDescriptorWrite':2,'WriteBufferFromFileDescriptorWriteBytes':187,'IOBufferAllocs':3,'IOBufferAllocBytes':3145773,'FunctionExecute':3,'DiskWriteElapsedMicroseconds':13,'InsertedRows':2,'InsertedBytes':16,'SelectedRows':4,'SelectedBytes':48,'ContextLock':16,'RWLockAcquiredReadLocks':1,'RealTimeMicroseconds':698,'SoftPageFaults':4,'OSReadChars':463}
  19. status:                  QueryFinish
  20. exception_code:          0
  21. exception:
  22. stack_trace:

See Also

  • system.query_log — Description of the query_log system table which contains common information about queries execution.
  • system.query_thread_log — This table contains information about each query execution thread.