![[PukiWiki]](image/owl.png "[PukiWiki]")
workgroupで設定した出力s3 bucketの形式はCSV固定。
任意のs3 bucketや指定形式で出力したい場合。
UNLOAD (SELECT * FROM "example"."user") TO 's3://example/user/tsv/' WITH (format = 'TEXTFILE', field_delimiter = '\t')
UNLOAD (SELECT id, name, YEAR, MONTH, DAY FROM "example"."user") TO 's3://example/user/tsv/' WITH (format = 'TEXTFILE', field_delimiter = '\t', partitioned_by = ARRAY['year','month','day'])
SELECT * FROM ( VALUES (1, 'a'), (2, 'b'), (3, 'c') ) AS t (id, name)
s3:/bucket/prefix/id=<id>/dt=<YYYY>-<MM>-<DD>/file.csv.gz s3:/bucket/prefix/id=<id>/dt=<YYYY>-<MM>-<DD>-<HH>/file.csv.gz s3:/bucket/prefix/id=<id>/year=<YYYY>/month=<MM>/day=<DD>/file.csv.gz
SELECT * FROM t WHERE concat(t.year, t.month, t.day) BETWEEN date_format(CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Tokyo' - INTERVAL '7' DAY, '%Y%m%d') AND date_format(CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Tokyo' - INTERVAL '1' DAY, '%Y%m%d')
2018-12-24T02:34:48.895006Z
SELECT substr(TIME,1,13) || ':00:00Z' AS time_hour, COUNT(elb) AS request_count FROM example.alb_logs WHERE DATE = format_datetime(now() - INTERVAL '1' DAY,'YYYY/MM/dd') GROUP BY substr(TIME,1,13) ORDER BY time_hour ASC;
SELECT substr(TIME,1,16) || ':00Z' AS time_min, COUNT(elb) AS request_count FROM example.alb_logs WHERE DATE = format_datetime(now() - INTERVAL '1' DAY,'YYYY/MM/dd') GROUP BY substr(TIME,1,16) ORDER BY time_min ASC;
SELECT date_trunc('hour', from_iso8601_timestamp(TIME)) AS time_hour, COUNT(elb) AS request_count FROM example.alb_logs WHERE DATE = format_datetime(now() - INTERVAL '1' DAY,'YYYY/MM/dd') GROUP BY date_trunc('hour', from_iso8601_timestamp(TIME)) ORDER BY time_hour ASC;
SELECT date_trunc('minute', from_iso8601_timestamp(TIME)) AS time_min, COUNT(elb) AS request_count FROM example.alb_logs WHERE DATE = format_datetime(now() - INTERVAL '1' DAY,'YYYY/MM/dd') GROUP BY date_trunc('minute', from_iso8601_timestamp(TIME)) ORDER BY time_min ASC;
SELECT now() AS _now, YEAR(TIMESTAMP '2018-12-24 02:34:48 UTC') AS _year, MONTH(TIMESTAMP '2018-12-24 02:34:48 UTC') AS _month, DAY(TIMESTAMP '2018-12-24 02:34:48 UTC') AS _day, week(TIMESTAMP '2018-12-24 02:34:48 UTC') AS _week, HOUR(TIMESTAMP '2018-12-24 02:34:48 UTC') AS _hour, MINUTE(TIMESTAMP '2018-12-24 02:34:48 UTC') AS _minute, SECOND(TIMESTAMP '2018-12-24 02:34:48 UTC') AS _second; -- _now: 2020-08-01 02:03:04.953 UTC -- _year: 2018 -- _month: 12 -- _day: 24 -- _week: 52 -- _hour: 2 -- _minute: 34 -- _second: 48
SELECT current_timezone(); -- 結果: UTC SELECT now() AT TIME ZONE 'Asia/Tokyo'; -- 結果: 2020-07-31 12:56:13.373 Asia/Tokyo SELECT TIMESTAMP '2018-12-24 02:34:48 UTC' AT TIME ZONE 'Asia/Tokyo'; -- 結果: 2018-12-24 11:34:48.000 Asia/Tokyo
SELECT DATE '2018-12-24' AS DATE; -- 結果: 2018-12-24 SELECT DATE '2018/12/24' AS DATE; -- 結果: INVALID_CAST_ARGUMENT: Value cannot be cast to date: 2018-12-24 SELECT date_parse('2018-12-24','%Y/%m/%d') AS TIMESTAMP; -- 結果: 2018-12-24 00:00:00.000 -- iso8601は専用の関数がある。from_iso8601_date()もある SELECT from_iso8601_timestamp('2018-12-24T02:34:48Z') AS TIMESTAMP; -- 結果: 2018-12-24 02:34:48.000 UTC
-- 月だけ取得 SELECT format_datetime(now(), 'MM') -- 結果: 07 -- 今日 SELECT format_datetime(now(),'YYYY/MM/dd') AS DATE; -- 結果: 2020/07/31 -- 1ヶ月前 SELECT format_datetime(now() - INTERVAL '1' MONTH, 'YYYY/MM/dd') AS DATE; -- 結果: 2020/06/30
SELECT format_datetime(date_parse('06/Feb/2019:00:00:38 +0000', '%d/%b/%Y:%H:%i:%s +0000'),'YYYY-MM-dd HH:mm:ss') -- 結果: 2019-02-06 00:00:38
-- 月初を取得 SELECT date_trunc('month', now()); -- 結果: 2020-07-01 00:00:00.000 UTC SELECT date_trunc('minute', now()); -- 結果: 2020-09-07 04:17:00.000 UTC SELECT date_trunc('hour', now()); -- 結果: 2020-09-07 04:00:00.000 UTC
SELECT dataset.name, ...
SELECT json_extract(BLOB, '$.name') AS name
# 3ヶ月前〜 "projection.date.range" = "NOW-3MONTH,NOW", # 3年前〜 "projection.date.range" = "NOW-3YEARS,NOW",
HIVE形式で「s3://bucket/year=${year}/month=${month}/day=${day}」とパーティションを分けているとする。
2019〜YYYYまでをパーティションとして認識させたい。
TBLPROPERTIES ( ... 'projection.enabled'='true', "projection.year.type" = 'date', "projection.year.range" = '2019,NOW', "projection.year.format" = 'yyyy', "projection.year.interval" = '1' , "projection.year.interval.unit" = 'YEARS', 'projection.month.type'='integer', 'projection.month.range'='1,12', 'projection.month.interval'='1', 'projection.month.digits'='2', 'projection.day.type'='integer', 'projection.day.range'='1,31', 'projection.day.interval'='1', 'projection.day.digits'='2', 'storage.location.template'='s3://bucket/year=${year}/month=${month}/day=${day}' );
GENERIC_INTERNAL_ERROR: Text '2019' could not be parsed: Unable to obtain YearMonth from TemporalAccessor: {Year=2020},ISO of type java.time.format.ParsedTBLPROPERTIES ( ... 'projection.year.type'='integer', 'projection.year.range'='2019,2030', 'projection.year.interval'='1', 'projection.year.digits'='4', ...
SELECT * FROM <table> WHERE YEAR=YEAR(now() - INTERVAL '1' DAY) AND MONTH=MONTH(now() - INTERVAL '1' DAY) AND DAY=DAY(now() - INTERVAL '1' DAY)
TBLPROPERTIES ( ... "projection.date.type" = "date", "projection.date.range" = "2020/07/01,NOW", "projection.date.format" = "year=yyyy/month=MM/day=dd", "projection.date.interval" = "1" , "projection.date.interval.unit" = "DAYS",
実行順序:
SQL実行順序:
local sql=$(cat << EOD CREATE EXTERNAL TABLE IF NOT EXISTS dns ( Date string, QueryType string, Client string, SendReceive string, Protocol string, RecordType string, Query string, Results string ) PARTITIONED BY ( year int, month int, day int ) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' WITH SERDEPROPERTIES ( 'separatorChar' = ',', 'quoteChar' = '\"', 'escapeChar' = '\\\\' ) STORED AS TEXTFILE LOCATION 's3://${S3_BUCKET}/${prefix}id=DNS/' TBLPROPERTIES ( 'skip.header.line.count'='1', 'has_encrypted_data'='false' ) ; EOD )
s3:/bucket/prefix/id=<id>/dt=<YYYY>-<MM>-<DD>/file.csv.gz s3:/bucket/prefix/id=<id>/dt=<YYYY>-<MM>-<DD>-<HH>/file.csv.gz s3:/bucket/prefix/id=<id>/year=<YYYY>/month=<MM>/day=<DD>/file.csv.gz
Athenaは1TBのスキャンあたり、1回$5かかる。スキャンデータを減らす工夫をしないと、コストが非常に増えるまた遅い。
s3:/bucket/prefix/id=<id>/year=<YYYY>/month=<MM>/day=<DD>/file.csv.gz
MSCK REPAIR TABLE TABLE_NAME;
