Préparation de vos fichiers de données¶

Meilleures pratiques et limitations en matière de dimensionnement de fichiers¶

Pour optimiser les performances de chargement et éviter les limitations de taille, tenez compte des instructions de dimensionnement des fichiers de données suivantes. Notez que ces recommandations s’appliquent aux chargements de données en masse ainsi qu’au chargement continu avec Snowpipe.

SELECT SYSTEM$ENABLE_BEHAVIOR_CHANGE_BUNDLE('2024_08');

Max LOB size (16777216) exceeded

COPY INTO <table>
  FROM @~/<file>.json
  FILE_FORMAT = (TYPE = 'JSON' STRIP_OUTER_ARRAY = true);

{
  "ID": 1,
  "CustomerDetails": {
    "RegistrationDate": 158415500,
    "FirstName": "John",
    "LastName": "Doe",
    "Events": [
      {
        "Type": "LOGIN",
        "Time": 1584158401,
        "EventID": "NZ0000000001"
      },
      /* ... */
      /* this array contains thousands of elements */
      /* with total size exceeding 16 MB */
      /* ... */
      {
        "Type": "LOGOUT",
        "Time": 1584158402,
        "EventID": "NZ0000000002"
      }
    ]
  }
}

CREATE OR REPLACE TABLE mytable AS
  SELECT
    t1.$1:ID AS id,
    t1.$1:CustomerDetails:RegistrationDate::VARCHAR AS RegistrationDate,
    t1.$1:CustomerDetails:FirstName::VARCHAR AS First_Name,
    t1.$1:CustomerDetails:LastName::VARCHAR AS as Last_Name,
    t2.value AS Event
  FROM @json t1,
    TABLE(FLATTEN(INPUT => $1:CustomerDetails:Events)) t2;

+----+-------------------+------------+------------+------------------------------+
| ID | REGISTRATION_DATE | FIRST_NAME | LAST_NAME  | EVENT                        |
|----+-------------------+------------+------------+------------------------------|
| 1  | 158415500         | John       | Doe        | {                            |
|    |                   |            |            |   "EventID": "NZ0000000001", |
|    |                   |            |            |   "Time": 1584158401,        |
|    |                   |            |            |   "Type": "LOGIN"            |
|    |                   |            |            | }                            |
|                     ... thousands of rows ...                                   |
| 1  | 158415500         | John       | Doe        | {                            |
|    |                   |            |            |   "EventID": "NZ0000000002", |
|    |                   |            |            |   "Time": 1584158402,        |
|    |                   |            |            |   "Type": "LOGOUT"           |
|    |                   |            |            | }                            |
+----+-------------------+------------+------------+------------------------------+

CREATE OR REPLACE TABLE mytable (
  id VARCHAR,
  registration_date VARCHAR(16777216),
  first_name VARCHAR(16777216),
  last_name VARCHAR(16777216),
  event VARCHAR(16777216));

INSERT INTO mytable
  SELECT
    t1.$1:ID,
    t1.$1:CustomerDetails:RegistrationDate::VARCHAR,
    t1.$1:CustomerDetails:FirstName::VARCHAR,
    t1.$1:CustomerDetails:LastName::VARCHAR,
    t2.value
  FROM @json t1,
    TABLE(FLATTEN(INPUT => $1:CustomerDetails:Events)) t2;

<?xml version='1.0' encoding='UTF-8'?>
<osm version="0.6" generator="osmium/1.14.0">
  <node id="197798" version="17" timestamp="2021-09-06T17:01:27Z" />
  <node id="197824" version="7" timestamp="2021-08-04T23:17:18Z" >
    <tag k="highway" v="traffic_signals"/>
  </node>
  <!--  thousands of node elements with total size exceeding 16 MB -->
  <node id="197826" version="4" timestamp="2021-08-04T16:43:28Z" />
</osm>

CREATE OR REPLACE TABLE mytable AS
  SELECT
    value:"@id" AS id,
    value:"@version" AS version,
    value:"@timestamp"::datetime AS TIMESTAMP,
    value:"$" AS tags
  FROM @mystage,
    LATERAL FLATTEN(INPUT => $1:"$")
  WHERE value:"@" = 'node';

+--------+---------+-------------------------+---------------------------------------------+
| ID     | VERSION | TIMESTAMP               | TAGS                                        |
|--------+---------+-------------------------+---------------------------------------------|
| 197798 | 17      | 2021-09-06 17:01:27.000 | ""                                          |
| 197824 | 7       | 2021-08-04 23:17:18.000 | <tag k="highway" v="traffic_signals"></tag> |
|                   ... thousands of rows ...                                              |
| 197826 | 4       | 2021-08-04 16:43:28.000 | ""                                          |
+--------+---------+-------------------------+---------------------------------------------+

CREATE OR REPLACE TABLE mytable AS
  SELECT
    ST_SIMPLIFY($1:geo, 10) AS geo
  FROM @mystage;

CREATE OR REPLACE TABLE mytable (
  id VARCHAR,
  registration_date VARCHAR,
  first_name VARCHAR,
  last_name VARCHAR);

COPY INTO mytable (
  id,
  registration_date,
  first_name,
  last_name
) FROM (
    SELECT
      $1:ID,
      $1:CustomerDetails::OBJECT:RegistrationDate::VARCHAR,
      $1:CustomerDetails::OBJECT:FirstName::VARCHAR,
      $1:CustomerDetails::OBJECT:LastName::VARCHAR
    FROM @mystage
);

Chargements de données en continu (c.-à-d., Snowpipe) et dimensionnement de fichiers¶

Snowpipe est conçu pour charger de nouvelles données typiquement dans la minute qui suit l’envoi d’une notification de fichier ; cependant, le chargement peut prendre beaucoup plus de temps pour de très gros fichiers ou dans les cas où une quantité inhabituelle de ressources de calcul est nécessaire pour décompresser, déchiffrer et transformer les nouvelles données.

En plus de consommer des ressources, les frais généraux de gestion des fichiers dans la file d’attente de chargement interne sont inclus dans les coûts d’utilisation facturés pour Snowpipe. Ces frais généraux augmentent en fonction du nombre de fichiers mis en file d’attente pour le chargement. Ces frais généraux apparaissent comme des frais de Snowpipe dans votre relevé de facturation parce que Snowpipe est utilisé pour les notifications d’événements pour les rafraîchissements automatiques des tables externes.

Pour optimiser l’expérience de chargement avec Snowpipe, nous vous recommandons de suivre les recommandations de dimensionnement des fichiers décrites dans Meilleures pratiques et limitations en matière de taille de fichier (dans cette rubrique). Le chargement de fichiers de données d’environ 100-250 MB ou plus réduit les frais généraux par rapport à la quantité totale de données chargées au point où les frais généraux sont négligeables.

S’il faut plus d’une minute pour accumuler des MBs de données dans votre application source, pensez à créer un nouveau fichier de données (potentiellement plus petit) une fois par minute. Cette approche conduit généralement à un bon équilibre entre le coût (c’est-à-dire les ressources consacrées à la gestion des files d’attente et la charge réelle) et la performance (c’est-à-dire la latence de la charge).

La création de fichiers de données plus petits et leur mise en zone de préparation dans un stockage Cloud plus fréquente qu’une fois par minute présentent les inconvénients suivants :

• Une réduction de la latence entre la mise en zone de préparation et le chargement des données ne peut être garantie.

• Les frais généraux de gestion des fichiers dans la file d’attente de chargement interne sont inclus dans les coûts d’utilisation facturés pour Snowpipe. Ces frais généraux augmentent en fonction du nombre de fichiers mis en file d’attente pour le chargement.

Différents outils permettent d’agréger et de regrouper des fichiers de données par lots. Option pratique : Amazon Data Firehose. Firehose permet de définir à la fois la taille de fichier souhaitée, appelée taille tampon, et l’intervalle d’attente après lequel un nouveau fichier est envoyé (vers le stockage Cloud, dans ce cas), appelé intervalle tampon. Pour plus d’informations, voir la documentation sur Amazon Data Firehose. Si votre application source accumule généralement suffisamment de données en une minute pour remplir des fichiers plus importants que la taille maximale recommandée pour un traitement parallèle optimal, vous pouvez réduire la taille de tampon pour déclencher la livraison de fichiers plus petits. Maintenir le réglage de l’intervalle tampon à 60 secondes (la valeur minimale) permet d’éviter de créer trop de fichiers ou d’augmenter la latence.

Préparation de fichiers texte délimités¶

Tenez compte des directives suivantes lorsque vous préparez vos fichiers texte délimités (CSV) au chargement :

• UTF-8 est le jeu de caractères par défaut. Cependant, des encodages supplémentaires sont pris en charge. Utilisez l’option de format de fichier ENCODING pour spécifier le jeu de caractères des fichiers de données. Pour plus d’informations, voir CREATE FILE FORMAT.

• Les champs qui contiennent des caractères de délimitation doivent être entourés de guillemets (simples ou doubles). Si les données contiennent des guillemets simples ou doubles, alors ces guillemets doivent faire l’objet d’un échappement.

• Les retours chariot sont généralement introduits sur les systèmes Windows en conjonction avec un caractère de saut de ligne pour marquer la fin d’une ligne (\r \n). Les champs qui contiennent des retours chariot doivent également être entourés de guillemets (simples ou doubles).

• Le nombre de colonnes dans chaque ligne doit être cohérent.

Fichiers de données semi-structurées et mise en colonnes¶

Lorsque des données semi-structurées sont insérées dans une colonne VARIANT, Snowflake applique certaines règles pour extraire autant de données que possible sous forme de colonnes. Le reste des données est stocké sous la forme d’une seule colonne dans une structure semi-structurée analysée.

Par défaut, Snowflake extrait un maximum de 200 éléments par partition, par table. Pour augmenter cette limite, contactez le support Snowflake.

Lignes directrices sur les données numériques¶

• Évitez les caractères imbriqués, comme les virgules (p. ex, 123,456).

• Si un nombre comprend une composante fractionnaire, il doit être séparé de la partie entière du nombre par un point décimal (p. ex, 123456.789).

• Oracle uniquement. Les types NUMBER ou NUMERIC d’Oracle permettent une échelle arbitraire, ce qui signifie qu’ils acceptent des valeurs avec des composantes décimales même si le type de données n’a pas été défini avec une précision ou une échelle. Dans Snowflake, en revanche, les colonnes conçues pour les valeurs à composantes décimales doivent être définies avec une échelle afin de préserver la portion décimale.

Lignes directrices sur les données d’horodatage et de date¶

• Pour plus d’informations sur les formats pris en charge pour les données de date, d’heure et d’horodatage, voir Formats d’entrée et de sortie de la date et de l’heure.

• Oracle uniquement. Le type de données DATE d’Oracle peut contenir des informations de date ou d’horodatage. Si votre base de données Oracle comprend des colonnes DATE qui stockent également des informations temporelles, associez ces colonnes à un type de données TIMESTAMP dans Snowflake plutôt que DATE.