Comment configurer et exploiter Snowflake en 2026

#!/bin/bash
# Téléchargement et installation SnowSQL (macOS/Linux)
wget https://repo1.maven.org/maven2/net/snowflake/snowsql/1.3.5/snowsql-1.3.5-linux_x86_64.tgz

tar -xzf snowsql-1.3.5-linux_x86_64.tgz

sudo mv snowsql/ /opt/snowsql/

# Configuration ~/.snowsql/config
cat > ~/.snowsql/config << EOF
[connections]
accountname = xy12345.us-east-1
username = VOTRE_USER
password = VOTRE_MDP
warehouse = COMPUTE_WH
rolename = SYSADMIN
EOF

# Test de connexion
/opt/snowsql/snowsql -a xy12345.us-east-1 -u VOTRE_USER

USE ROLE SYSADMIN;

-- Suspendre si existant
ALTER WAREHOUSE IF EXISTS MON_WH SUSPEND;

-- Créer warehouse XS (1 credit/heure)
CREATE WAREHOUSE MON_WH
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 300
  AUTO_RESUME = TRUE
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 2
  SCALING_POLICY = 'STANDARD';

-- Utiliser le warehouse
USE WAREHOUSE MON_WH;

-- Vérifier statut
SELECT * FROM TABLE(INFORMATION_SCHEMA.WAREHOUSE_LOAD_HISTORY(MON_WH));

USE ROLE SYSADMIN;

-- Créer database avec rétention 7 jours
CREATE DATABASE IF NOT EXISTS MA_DB
  DATA_RETENTION_TIME_IN_DAYS = 7;

USE DATABASE MA_DB;

-- Créer schema
CREATE SCHEMA IF NOT EXISTS ANALYTICS;

USE SCHEMA ANALYTICS;

-- Table pour données ventes (clustering sur date_r)
CREATE TABLE ventes (
  id INT AUTOINCREMENT,
  produit VARCHAR(100),
  date_v NUMERIC(8,0),
  quantite INT,
  montant DECIMAL(10,2)
)
CLUSTER BY (date_v);

-- Insérer données test
INSERT INTO ventes (produit, date_v, quantite, montant) VALUES
('Laptop', 20260115, 1, 1200.00),
('Souris', 20260115, 10, 25.00);

-- Créer stage interne
CREATE STAGE mon_stage
  DIRECTORY = ( ENABLE = TRUE )
  ENCRYPTION = ( TYPE = 'SNOWFLAKE_SSE' );

-- Uploader fichier local (via SnowSQL ou WS)
PUT file:///chemin/vers/ventes.csv @mon_stage AUTO_COMPRESS=TRUE;

-- Copier dans table (format CSV par défaut)
COPY INTO ventes
FROM @mon_stage/ventes.csv.gz
FILE_FORMAT = (TYPE = CSV FIELD_OPTIONALLY_ENCLOSED_BY = '"' SKIP_HEADER = 1)
ON_ERROR = 'CONTINUE';

-- Vérifier
SELECT COUNT(*) FROM ventes;

-- Ventes par jour avec running total
SELECT
  date_v,
  SUM(montant) as total_jour,
  SUM(SUM(montant)) OVER (ORDER BY date_v ROWS UNBOUNDED PRECEDING) as running_total
FROM ventes
GROUP BY date_v;

-- Support JSON variant
CREATE TABLE events (
  data VARIANT
);

INSERT INTO events SELECT PARSE_JSON('{"user_id":1, "action":"click"}');

SELECT data:user_id::INT as user_id, data:action
FROM events
WHERE data:action = 'click';

-- Récupérer table à -1h (si rétention >0)
CREATE TABLE ventes_backup AS
SELECT * FROM ventes AT (OFFSET => -3600);

-- Clôner zero-copy (instantané, sans duplication storage)
CREATE TABLE ventes_prod CLONE ventes;

-- Mettre à jour clone (indépendant)
UPDATE ventes_prod SET montant = montant * 1.1 WHERE id = 1;

-- Vérifier métadonnées
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ventes_prod';

-- Stage externe S3 (exemple AWS)
CREATE STAGE my_s3_stage
  URL = 's3://mon-bucket/'
  CREDENTIALS = (AWS_KEY_ID='xxx' AWS_SECRET_KEY='yyy');

-- Pipe auto-ingest
CREATE PIPE ventes_pipe
  AUTO_INGEST = TRUE
  AS
  COPY INTO ventes
  FROM @my_s3_stage
  FILE_FORMAT = (TYPE = 'CSV' FIELD_DELIMITER = ',' SKIP_HEADER=1);

-- Notification S3 (Lambda ou NSQ)
-- Config via AWS SNS : arn:aws:sns:...

-- Vérifier pipes
SELECT * FROM TABLE(INFORMATION_SCHEMA.PIPE_STATUS('VENTES_PIPE'));
SHOW PIPES;