Redshift で COPY コマンド実行時に svl_query_metrics_summary や sys_query_history に情報が記録されることを確認したメモ。
結果
dev=# copy public.customer from 's3://test-rs-copy-bucket/csv/customer/' csv dev-# iam_role 'arn:aws:iam::123456789012:role/redshift-spectrum-s3-fullaccess'; INFO: Load into table 'customer' completed, 9000000 record(s) loaded successfully. COPY Time: 8538.877 ms (00:08.539) dev=# select pg_last_query_id(); pg_last_query_id ------------------ 33416134 (1 row) Time: 3.421 ms dev=# \gset Time: 3.452 ms dev=# \x Expanded display is on. dev=# select * from svl_query_metrics_summary where query = :pg_last_query_id; -[ RECORD 1 ]--------------+----------------------------------------------------------------- userid | 100 query | 33416134 service_class | 102 query_cpu_time | 14 query_blocks_read | 160 query_execution_time | 8 query_cpu_usage_percent | 40.07 query_temp_blocks_to_disk | segment_execution_time | 3 cpu_skew | 1.91 io_skew | 1.00 scan_row_count | 8850432 join_row_count | nested_loop_join_row_count | return_row_count | spectrum_scan_row_count | spectrum_scan_size_mb | query_queue_time | service_class_name | Default queue Time: 682.883 ms dev=# select * from sys_query_history where transaction_id = (select xid from stl_query where query = :pg_last_query_id); -[ RECORD 1 ]----+------------------------------------------------------------------------------------------------ user_id | 100 query_id | 33416133 query_label | default transaction_id | 93744987 session_id | 1073889466 database_name | dev query_type | COPY status | success result_cache_hit | f start_time | 2024-09-06 00:04:43.957806 end_time | 2024-09-06 00:04:51.574099 elapsed_time | 7616293 queue_time | 0 execution_time | 7491272 error_message | returned_rows | 0 returned_bytes | 0 query_text | copy public.customer from 's3://test-rs-copy-bucket/csv/customer/' csv\niam_role ''; redshift_version | 1.0.73348 usage_limit | compute_type | primary compile_time | 6539232 planning_time | 0 lock_wait_time | 36 Time: 790.980 ms
手順
create table public.customer ( c_custkey integer not null, c_name varchar(25) not null, c_address varchar(25) not null, c_city varchar(10) not null, c_nation varchar(15) not null, c_region varchar(12) not null, c_phone varchar(15) not null, c_mktsegment varchar(10) not null ) diststyle even compound sortkey(c_nation,c_region); copy public.customer from 's3://test-rs-copy-bucket/csv/customer/' csv iam_role 'arn:aws:iam::123456789012:role/redshift-spectrum-s3-fullaccess'; select pg_last_query_id(); \gset \x select * from svl_query_metrics_summary where query = :pg_last_query_id; select * from sys_query_history where transaction_id = (select xid from stl_query where query = :pg_last_query_id);
