snowflake support micro partition cluster by hour?
the doc says support clustering by
Expressions on base columns and has an example of using TO_DATE(timestamp) as clustering key,
that indeed works as I verified, but in my case, cluster by hour is better so use this DATE_TRUNC by HOUR as cluster key
CREATE OR REPLACE TABLE t( "timestamp" datetime, ... more fields ) CLUSTER BY (DATE_TRUNC('HOUR', "timestamp"));
the CREATE TABLE runs successfully, however, when loading data with COPY INTO, it says not supported within a COPY, so wonder is clustering by hour supported? or is the COPY command's limitation ? anyone knows a workaround?
作者: user5672998 的来源 发布者: 2017 年 12 月 27 日
002300 (0A000): SQL Compilation error: Function 'TRUNCTIMESTAMPTOHOUR' not supported within a COPY
As a workaround, try COPY into a staging table "stg" that does not have the Clustering key defined. Then insert to the target table:
作者: Stuart Ozer 发布者: 04.01.2018 10:01
INSERT INTO t SELECT * FROM stg ORDER BY (DATE_TRUNC('HOUR', "timestamp"));
Expanding on Stuart's answer: just perform an INSERT INTO, SELECT FROM [staging area].
作者: SimonD 发布者: 09.03.2018 05:47
INSERT INTO DB.SCHEMA.T(timestamp, col2, ...) SELECT $1::timestamp_ntz, $2 ... FROM @SOURCE.EXTRACTS/folder_location (FILE_FORMAT => '[file_format_name]', PATTERN => '[regex file pattern]')