Redshift Observatory

System Table Tracker

System view pg_catalog.svcs_s3list version 1.0.46806 / 2023-02-21

View Text

SELECT map.primary_query AS query,
       stcs.segment,
       stcs.node,
       CAST('1970-01-01 00:00:00' AS timestamp) + (CAST((CAST(stcs.eventtime AS numeric) / (1000.0 * 1000.0)) + 946684800.0 AS double precision) * CAST('00:00:01' AS interval)) AS eventtime,
       stcs.bucket,
       stcs.prefix,
       stcs.recursive,
       stcs.retrieved_files,
       stcs.max_file_size,
       stcs.avg_file_size,
       stcs.generated_splits,
       stcs.avg_split_length,
       stcs.duration
FROM stcs_s3list AS stcs
     INNER JOIN stcs_concurrency_scaling_query_mapping AS map ON map.concurrency_scaling_query = stcs.query
WHERE stcs.__cluster_type = CAST('cs' AS bpchar)
  AND to_date(CAST(stcs.__log_generated_date AS text),
              CAST('YYYYMMDD' AS text)) > (getdate() - CAST('7 days' AS interval))
  AND to_date(CAST(map.__log_generated_date AS text),
              CAST('YYYYMMDD' AS text)) > (getdate() - CAST('7 days' AS interval))
  AND CAST(map.concurrency_scaling_cluster AS text) = split_part(CAST(stcs.__path AS text),
                                                                 CAST('/' AS text),
                                                                 10)
  AND (EXISTS (SELECT 1
               FROM pg_user
               WHERE pg_user.usename = CAST("current_user"() AS name)
                 AND pg_user.usesuper = TRUE)
    OR EXISTS (SELECT 1
               FROM pg_shadow_extended
               WHERE pg_shadow_extended.sysid = current_user_id()
                 AND pg_shadow_extended.colnum = 2
                 AND pg_shadow_extended.value = CAST(-1 AS text))
    OR stcs.userid = current_user_id())
  AND stcs.scan_type = 2

UNION ALL

SELECT svl_s3list.query,
       svl_s3list.segment,
       svl_s3list.node,
       svl_s3list.eventtime,
       CAST(svl_s3list.bucket AS varchar) AS bucket,
       CAST(svl_s3list.prefix AS varchar) AS prefix,
       svl_s3list.recursive,
       svl_s3list.retrieved_files,
       svl_s3list.max_file_size,
       svl_s3list.avg_file_size,
       svl_s3list.generated_splits,
       svl_s3list.avg_split_length,
       svl_s3list.duration
FROM svl_s3list

Home 3D Друк Blog Bring-Up Times Consultancy Cross-Region Benchmarks Email Forums IRC Mailing Lists Reddit Redshift Price Tracker Redshift Version Tracker Redshift Workbench System Table Tracker The Known Universe Twitter White Papers