This article is a Private article. Only a writer and users who know the URL can access it.
Please change open range to public in publish setting if you want to share this article with other users.

snowflakeで使うSQLまとめ

Snowflake

Last updated at 2025-01-17Posted at 2024-10-30

キャッシュのオンオフ

SHOW PARAMETERS LIKE '%USE_CACHED_RESULT%';
ALTER ACCOUNT SET USE_CACHED_RESULT = false;  -- アカウント単位
ALTER SESSION SET USE_CACHED_RESULT = false; -- セッション単位（基本はこっち使う

タイムゾーンの変更

SHOW PARAMETERS LIKE '%TIMEZONE%';
select current_timestamp;
ALTER ACCOUNT SET TIMEZONE = 'Asia/Tokyo';
select current_timestamp;

ダブルコーテーションされた修飾子の大文字小文字を無視する

SHOW PARAMETERS like 'QUOTED_IDENTIFIERS_IGNORE_CASE';
ALTER SESSION SET QUOTED_IDENTIFIERS_IGNORE_CASE = true; -- セッション単位

指定日のクレジット数

select *
from SNOWFLAKE.ACCOUNT_USAGE.METERING_DAILY_HISTORY
where usage_date = '2024-10-29' limit 10;

指定日のクエリ実行時間

select sum(total_elapsed_time)/1000 as sec, sum(total_elapsed_time)/1000/60 as min
from SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
where CONVERT_TIMEZONE('Asia/Tokyo',start_time)::date = '2024-10-29' limit 1;

変数をつかう

select 用

set VAL1='abcdefg';
select $VAL1;
select * from hoge_table where hoge_col = $VAL1 limit 1;

create table 用

-- 環境指定
set env = 'dev';

-- 環境名 + テーブル名
set table_test = $env || '_test_val_table';
select $env;
select $table_test;

-- 変数を使ってテーブル作成
create table IDENTIFIER($table_test) (
    id integer
);

desc table dev_test_val_table;
--drop table dev_test_val_table;

declare
    hoge RESULTSET;
    moge integer default 100;
begin
    let poge integer default 200;
    hoge := (select current_date as a, :moge as moge, :poge as poge);
    return table(hoge);
end

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up