Snowflake

Supported OS Linux Mac OS Windows

Présentation

Ce check permet de surveiller Snowflake via l’Agent Datadog. Snowflake est un entrepôt de données analytique fourni en tant que SaaS et s’exécute entièrement sur une infrastructure cloud. Cette intégration permet de surveiller l’utilisation des crédits, la facturation, le stockage, l’historique des requêtes et bien plus encore.

REMARQUE : les métriques sont collectées par le biais de requêtes envoyées à Snowflake. Les requêtes transmises par l'intégration Datadog sont facturables par Snowflake.

Configuration

Suivez les instructions ci-dessous pour installer et configurer ce check lorsque l’Agent est exécuté sur un host.

Installation

Le check Snowflake est inclus avec le package de l’Agent Datadog . Vous n’avez donc rien d’autre à installer sur votre serveur.

Remarque : actuellement, le check Snowflake n’est pas disponible pour macOS dans l’Agent Datadog 6 avec Python 2.

Pour les utilisateurs qui configurent l'intégration avec l'Agent v7.23.0, passez à la version 2.0.1 de l'intégration pour tirer profit des dernières nouveautés. Pour mettre à niveau l'intégration, utilisez cette commande :
datadog-agent integration install datadog-snowflake==2.0.1

Configuration

Snowflake recommande d'accorder des autorisations à un rôle alternatif tel que `SYSADMIN`. En savoir plus sur le contrôle du rôle ACCOUNTADMIN (en anglais).
  1. Créez un rôle et un utilisateur spécifiques Datadog pour surveiller Snowflake. Dans Snowflake, exécutez les commandes suivantes pour créer un rôle personnalisé ayant accès au schéma ACCOUNT_USAGE.

    Remarque : par défaut, cette intégration surveille la base de données SNOWFLAKE et le schéma ACCOUNT_USAGE. Cette base de données est disponible par défaut et ne peut être consultée que par les utilisateurs disposant du rôle ACCOUNTADMIN ou de tout rôle accordé par ACCOUNTADMIN .

    use role ACCOUNTADMIN;
    grant imported privileges on database snowflake to role SYSADMIN;
    
    use role SYSADMIN;
    

    Vous pouvez également créer un rôle personnalisé DATADOG ayant accès à ‘ACCOUNT_USAGE.

    -- Créer un nouveau rôle destiné à surveiller l'utilisation de Snowflake
    create role DATADOG;
    
    -- Accorder au nouveau rôle des autorisations pour la base de données SNOWFLAKE
    grant imported privileges on database SNOWFLAKE to role DATADOG;
    
    -- Créer un utilisateur (ignorer cette étape en cas d'utilisation d'un utilisateur existant)
    create user DATADOG_USER
    LOGIN_NAME = DATADOG_USER
    password = '<MOT_DE_PASSE>'
    default_warehouse = <ENTREPÔT>
    default_role = DATADOG
    default_namespace = SNOWFLAKE.ACCOUNT_USAGE;
    
    -- Accorder le rôle de monitor à l'utilisateur
    grant role DATADOG to user <UTILISATEUR>;
    
  2. Modifiez le fichier snowflake.d/conf.yaml dans le dossier conf.d/ à la racine du répertoire de configuration de votre Agent pour commencer à recueillir vos données de performance Snowflake. Consultez le fichier d’exemple snowflake.d/conf.yaml pour découvrir toutes les options de configuration disponibles.

        ## @param account - string - required
        ## Name of your account (provided by Snowflake), including the platform and region if applicable.
        ## For more information on Snowflake account names,
        ## see https://docs.snowflake.com/en/user-guide/connecting.html#your-snowflake-account-name
        #
      - account: <ACCOUNT>
    
        ## @param user - string - required
        ## Login name for the user.
        #
        user: <USER>
    
        ## @param password - string - required
        ## Password for the user
        #
        password: <PASSWORD>
    
        ## @param role - string - required
        ## Name of the role to use.
        ##
        ## By default, the SNOWFLAKE database is only accessible by the ACCOUNTADMIN role. Snowflake recommends
        ## configuring a role specific for monitoring:
        ## https://docs.snowflake.com/en/sql-reference/account-usage.html#enabling-account-usage-for-other-roles
        #
        role: <ROLE>
    
        ## @param min_collection_interval - number - optional - default: 3600
        ## This changes the collection interval of the check. For more information, see:
        ## https://docs.datadoghq.com/developers/write_agent_check/#collection-interval
        ##
        ## NOTE: Most Snowflake ACCOUNT_USAGE views are populated on an hourly basis,
        ## so to minimize unnecessary queries the `min_collection_interval` defaults to 1 hour.
        #
        min_collection_interval: 3600
    
    By default, the min_collection_interval is 1 hour. Snowflake metrics are aggregated by day, you can increase the interval to reduce the number of queries.
    Note: Snowflake ACCOUNT_USAGE views have a known latency of 45 minutes to 3 hours.
  3. Redémarrez l’Agent .

Configuration d’un proxy

Snowflake recommande de définir des variables d’environnement pour configurer un proxy .

Vous pouvez également définir proxy_host, proxy_port, proxy_user et proxy_password sous init_config dans le fichier snowflake.d/conf.yaml .

REMARQUE : Snowflake met automatiquement en forme les configurations de proxy et définit les variables d’environnement de proxy standard . Ces variables influent sur l’ensemble des requêtes provenant des intégrations, y compris les services d’orchestration comme Docker, ECS et Kubernetes.

Requêtes personnalisées Snowflake

L’intégration Snowflake prend en charge les requêtes personnalisées. Par défaut, elle interagit avec la base de données partagée SNOWFLAKE et le schéma ACCOUNT_USAGE.

Si vous souhaitez exécuter des requêtes personnalisées avec un autre schéma ou une autre base de données, ajoutez une autre instance au fichier d’exemple snowflake.d/conf.yaml et configurez les options database et schema. Vérifiez que l’utilisateur et le rôle ont accès à la base de données ou au schéma indiqué.

Options de configuration

custom_queries dispose des options suivantes :

OptionObligatoireDescription
queryOuiLe SQL à exécuter. Il peut s’agir d’une simple déclaration ou d’un script sur plusieurs lignes. Toutes les rangées des résultats sont évaluées. Utilisez le symbole pipe « 
columnsOuiListe représentant toutes les colonnes, triées par ordre séquentiel de gauche à droite.

Deux types d’informations sont obligatoires :
-name : le suffixe à ajouter à metric_prefix afin de former un nom de métrique complet. Si le type est défini sur tag, la colonne est considérée comme un tag et appliquée à chaque métrique recueillie par cette requête.
- type : la méthode d’envoi (gauge, count, rate, etc.). Cette option peut également être définie sur tag pour ajouter à chaque métrique de la rangée le nom et la valeur (<nom>:<valeur_rangée>) de l’élément en tant que tag dans cette colonne.
tagsNonLa liste des tags statiques à appliquer à chaque métrique.
Remarques
  • Au moins un élément de columns doit correspondre à un type de métrique (gauge, count, rate, etc.).
  • Le nombre d’éléments dans columns doit correspondre au nombre de colonnes renvoyées par la requête.
  • L’ordre des éléments dans columns doit correspondre à celui des valeurs renvoyées par la requête.
custom_queries:
  - query: select F3, F2, F1 from Table;
    columns:
      - name: f3_metric_alias
        type: gauge
      - name: f2_tagkey
        type: tag
      - name: f1_metric_alias
        type: count
    tags:
      - test:snowflake

Exemple

La requête suivante compte toutes les requêtes de la vue QUERY_HISTORY avec un tag comportant les noms de la base de données, du schéma et de l’entrepôt.

select count(*), DATABASE_NAME, SCHEMA_NAME, WAREHOUSE_NAME from QUERY_HISTORY group by 2, 3, 4;
Configuration

Voici un exemple de configuration de requêtes personnalisées dans instances :

custom_queries:
  - query: select count(*), DATABASE_NAME, SCHEMA_NAME, WAREHOUSE_NAME from QUERY_HISTORY group by 2, 3, 4;
    columns:
      - name: query.total
        type: gauge
      - name: database_name
        type: tag
      - name: schema_name
        type: tag
      - name: warehouse_name
        type: tag
    tags:
      - test:snowflake
Validation

Pour vérifier le résultat, recherchez les métriques à l’aide du résumé des métriques  :

Résumé des métriques Snowflake

Validation

Lancez la sous-commande status de l’Agent et cherchez snowflake dans la section Checks.

Données collectées

Métriques

snowflake.storage.storage_bytes.total
(gauge)
The average number of bytes of table storage used including bytes for data currently in Time Travel.
Shown as byte
snowflake.storage.stage_bytes.total
(gauge)
The average number of bytes of stage storage used by files in all internal stages.
Shown as byte
snowflake.storage.failsafe_bytes.total
(gauge)
The average number of bytes of data in Fail-safe.
Shown as byte
snowflake.storage.database.storage_bytes
(gauge)
The average number of bytes of database storage used.
Shown as byte
snowflake.storage.database.failsafe_bytes
(gauge)
The average number of bytes of Fail-safe storage used.
Shown as byte
snowflake.billing.virtual_warehouse.avg
(gauge)
The average overall credits billed per hour for virtual warehouses.
Shown as unit
snowflake.billing.virtual_warehouse.sum
(gauge)
The sum of overall credits billed for virtual warehouses.
Shown as unit
snowflake.billing.cloud_service.avg
(gauge)
The average overall credits billed per hour for cloud services.
Shown as unit
snowflake.billing.cloud_service.sum
(gauge)
The total overall credits billed for cloud services.
Shown as unit
snowflake.billing.total_credit.avg
(gauge)
The average overall of credits used per hour for the account. This is the sum of snowflake.billing.cloudservice and snowflake.billing.virtualwarehouses.
Shown as unit
snowflake.billing.total_credit.sum
(gauge)
The total overall of credits used for the account. This is the sum of snowflake.billing.cloudservice and snowflake.billing.virtualwarehouses.
Shown as unit
snowflake.billing.warehouse.cloud_service.avg
(gauge)
The average credits used per hour for cloud services by warehouse.
Shown as unit
snowflake.billing.warehouse.cloud_service.sum
(gauge)
The total credits used for cloud services by warehouse.
Shown as unit
snowflake.billing.warehouse.virtual_warehouse.avg
(gauge)
The average credits used per hour by warehouse.
Shown as unit
snowflake.billing.warehouse.virtual_warehouse.sum
(gauge)
The total credits used by warehouse.
Shown as unit
snowflake.billing.warehouse.total_credit.avg
(gauge)
The average total number of credits used per hour for the warehouse. NOTE: This value does not take into account adjustments which may be higher than your actual credit consumption.
Shown as unit
snowflake.billing.warehouse.total_credit.sum
(gauge)
The total number of credits used for the warehouse. NOTE: This value does not take into account adjustments which may be higher than your actual credit consumption.
Shown as unit
snowflake.logins.fail.count
(count)
Total failed login attempts.
Shown as error
snowflake.logins.success.count
(count)
Total successful login attempts.
Shown as success
snowflake.logins.total
(count)
Total number of login attempts.
Shown as attempt
snowflake.query.executed
(gauge)
The average number of queries executed.
Shown as query
snowflake.query.queued_overload
(gauge)
Average number of queries queued because the warehouse was overloaded.
Shown as query
snowflake.query.queued_provision
(gauge)
Average number of queries queued because the warehouse was being provisioned.
Shown as query
snowflake.query.blocked
(gauge)
Average number of queries blocked by a transaction lock.
Shown as query
snowflake.query.execution_time
(gauge)
Average query execution time.
Shown as millisecond
snowflake.query.compilation_time
(gauge)
Average query compilation time.
Shown as millisecond
snowflake.query.bytes_scanned
(gauge)
Average query bytes scanned.
Shown as byte
snowflake.query.bytes_written
(gauge)
Average query bytes written.
Shown as byte
snowflake.query.bytes_deleted
(gauge)
Average query bytes deleted.
Shown as byte
snowflake.data_transfer.bytes.avg
(gauge)
Average bytes transferred.
Shown as byte
snowflake.data_transfer.bytes.sum
(gauge)
Total bytes transferred.
Shown as byte
snowflake.auto_recluster.credits_used.avg
(gauge)
Average credits billed for automatic reclustering.
Shown as unit
snowflake.auto_recluster.credits_used.sum
(gauge)
Total credits billed for automatic reclustering.
Shown as unit
snowflake.auto_recluster.bytes_reclustered.avg
(gauge)
Average bytes reclustered.
Shown as byte
snowflake.auto_recluster.bytes_reclustered.sum
(gauge)
Total bytes reclustered.
Shown as byte
snowflake.auto_recluster.rows_reclustered.avg
(gauge)
Average rows reclustered.
Shown as row
snowflake.auto_recluster.rows_reclustered.sum
(gauge)
Total rows reclustered.
Shown as row
snowflake.storage.table.active_bytes.avg
(gauge)
Average bytes owned by (and billed to) this table that are in the active state.
Shown as byte
snowflake.storage.table.time_travel_bytes.avg
(gauge)
Average bytes owned by (and billed to) this table that are in the Time Travel state.
Shown as byte
snowflake.storage.table.failsafe_bytes.avg
(gauge)
Average bytes owned by (and billed to) this table that are in the Fail-safe state.
Shown as byte
snowflake.storage.table.retained_bytes.avg
(gauge)
Average bytes owned by (and billed to) this table that are retained after deletion because they are referenced by one or more clones of this table.
Shown as byte
snowflake.pipe.credits_used.avg
(gauge)
Average number of credits billed for Snowpipe data loads.
Shown as unit
snowflake.pipe.credits_used.sum
(gauge)
Total number of credits billed for Snowpipe data loads.
Shown as unit
snowflake.pipe.bytes_inserted.avg
(gauge)
Average number of bytes loaded from Snowpipe.
Shown as byte
snowflake.pipe.bytes_inserted.sum
(gauge)
Total number of bytes loaded from Snowpipe.
Shown as byte
snowflake.pipe.files_inserted.avg
(gauge)
Average number of files loaded from Snowpipe.
Shown as file
snowflake.pipe.files_inserted.sum
(gauge)
Total number of files loaded from Snowpipe.
Shown as file
snowflake.replication.credits_used.avg
(gauge)
Average number of credits used for database replication.
Shown as unit
snowflake.replication.credits_used.sum
(gauge)
Total number of credits used for database replication.
Shown as unit
snowflake.replication.bytes_transferred.avg
(gauge)
Average number of bytes transferred for database replication.
Shown as byte
snowflake.replication.bytes_transferred.sum
(gauge)
Total number of bytes transferred for database replication.
Shown as byte
snowflake.query.bytes_spilled.local
(gauge)
Avg volume of data spilled to local disk.
Shown as byte
snowflake.query.bytes_spilled.remote
(gauge)
Avg volume of data spilled to remote disk.
Shown as byte
snowflake.organization.contract.amount
(gauge)
The average amount for the given contract
snowflake.organization.credit.virtual_warehouse.sum
(gauge)
The sum of credits used by warehouse.
snowflake.organization.credit.virtual_warehouse.avg
(gauge)
The average credits used per day by warehouse.
snowflake.organization.credit.cloud_service.sum
(gauge)
The sum of credits billed for cloud services
snowflake.organization.credit.cloud_service.avg
(gauge)
The average overall credits billed for cloud services
snowflake.organization.credit.cloud_service_adjustment.sum
(gauge)
Sum of credits adjusted for included cloud services.
snowflake.organization.credit.cloud_service_adjustment.avg
(gauge)
Average of credits adjusted for included cloud services.
snowflake.organization.credit.total_credit.sum
(gauge)
The sum of overall of credits used for the organization. This is the sum of snowflake.organization.credit.cloudservice.sum and snowflake.organization.credit.virtualwarehouse.sum.
snowflake.organization.credit.total_credit.avg
(gauge)
The average number of overall of credits used for the organization. This is the sum of snowflake.organization.credit.cloudservice.avg and snowflake.organization.credit.virtualwarehouse.avg.
snowflake.organization.credit.total_credits_billed.sum
(gauge)
The total number of credits billed for the account in the day. It is the sum of snowflake.organization.credit.cloudservice.sum, snowflake.organization.credit.virtualwarehouse.sum, and snowflake.organization.credit.cloudserviceadjustment.sum.
snowflake.organization.credit.total_credits_billed.avg
(gauge)
The average number of credits billed for the account in the day. It is the sum of snowflake.organization.credit.cloudservice.avg, snowflake.organization.credit.virtualwarehouse.avg, and snowflake.organization.credit.cloudserviceadjustment.avg.
snowflake.organization.currency.usage
(gauge)
Sum of the total number of credits charged per day.
snowflake.organization.currency.usage_in_currency
(gauge)
Sum of the total amount charged for the day.
snowflake.organization.warehouse.virtual_warehouse.sum
(gauge)
Sum of number of credits used for the warehouse.
snowflake.organization.warehouse.virtual_warehouse.avg
(gauge)
Average number of credits used for the warehouse.
snowflake.organization.warehouse.cloud_service.avg
(gauge)
Average number of credits used for cloud services.
snowflake.organization.warehouse.cloud_service.sum
(gauge)
Sum of credits used for cloud services.
snowflake.organization.warehouse.total_credit.sum
(gauge)
The total number of credits used by the warehouse. Sum of snowflake.organization.warehouse.cloudservice.sum and snowflake.organization.warehouse.totalcredit.sum.
snowflake.organization.warehouse.total_credit.avg
(gauge)
The average total number of credits used by the warehouse. Sum of snowflake.organization.warehouse.cloudservice.avg and snowflake.organization.warehouse.totalcredit.avg
snowflake.organization.storage.average_bytes
(gauge)
Number of bytes of database storage used, including data in Time Travel and Fail-safe.
Shown as byte
snowflake.organization.storage.credits
(gauge)
Sum of total number of credits used for all accounts in the organization. Sum of credits used for database storage and stages.
snowflake.organization.rate.effective_rate
(gauge)
The rate after applying any applicable discounts per the contract for the organization.
snowflake.organization.data_transfer.bytes_transferred
(gauge)
Number of bytes transferred during the usage date.
Shown as byte
snowflake.organization.balance.capacity
(gauge)
The amount of capacity in currency that is available for use. This is the end of day balance.
snowflake.organization.balance.on_demand_consumption
(gauge)
The amount of consumption at on demand prices that will be invoiced given that all the free usage and capacity balances have been exhausted. This is a negative value (e.g. -250) until the invoice is paid. This is the end of day balance.
snowflake.organization.balance.rollover
(gauge)
The amount of rollover balance in currency that is available for use. At the end of a contract term
snowflake.organization.balance.free_usage
(gauge)
The amount of free usage in currency that is available for use. This is the end of day balance

Checks de service

snowflake.can_connect :
Renvoie CRITICAL si l’Agent ne parvient pas à s’authentifier et à se connecter à Snowflake. Si ce n’est pas le cas, renvoie OK.

Événements

Snowflake n’inclut aucun événement.

Dépannage

Besoin d’aide ? Contactez l’assistance Datadog .