Integración con Snowflake usando Flex

Nuestra integración con Snowflake le permite recopilar datos completos sobre varios aspectos, incluido el rendimiento de la consulta, el estado del sistema de almacenamiento, el estado del almacén y la información de facturación.

A screenshot of a dashboard with Snowflake query metrics.

Después de configurar la integración de Snowflake con New Relic, vea sus datos en un dashboard como este, nada más sacarlo de la caja.

Instalar el agente de infraestructura

Para emplear la integración de Snowflake, también debe instalar el agente de infraestructura en el mismo host. El agente de infraestructura monitorea el host en sí, mientras que la integración que instalará en el siguiente paso extiende su monitoreo con datos específicos de Snowflake.

Configuración de métricas Snowflake

Ejecute el siguiente comando para almacenar Snowflake métrica en formato JSON, permitiendo que nri-flex lo lea. Cerciorar de modificar ACCOUNT, USERNAME y SNOWSQL_PWD según corresponda.

bash

$# Run the below command as a 1 minute cronjob
$    SNOWSQL_PWD='Replaceme' snowsql -o output_format=json -o remove_comments=true -o header=true -o timing=false -o friendly=false -a <ACCOUNT> -u <USERNAME> -q 'SELECT SERVICE_TYPE, NAME, AVG(CREDITS_USED_COMPUTE) AS "CREDITS_USED_COMPUTE_AVERAGE", SUM(CREDITS_USED_COMPUTE) AS "CREDITS_USED_COMPUTE_SUM", AVG(CREDITS_USED_CLOUD_SERVICES) AS "CREDITS_USED_CLOUD_SERVICES_AVERAGE", SUM(CREDITS_USED_CLOUD_SERVICES) AS "CREDITS_USED_CLOUD_SERVICES_SUM", AVG(CREDITS_USED) AS "CREDITS_USED_AVERAGE", SUM(CREDITS_USED) AS "CREDITS_USED_SUM" FROM "SNOWFLAKE"."ACCOUNT_USAGE"."METERING_HISTORY" WHERE start_time >= DATE_TRUNC(day, CURRENT_DATE()) GROUP BY 1, 2;' > /tmp/snowflake-account-metering.json
$    SNOWSQL_PWD='Replaceme' snowsql -o output_format=json -o remove_comments=true -o header=true -o timing=false -o friendly=false -a <ACCOUNT> -u <USERNAME> -q 'SELECT WAREHOUSE_NAME, AVG(AVG_RUNNING) AS "RUNNING_AVERAGE", AVG(AVG_QUEUED_LOAD) AS "QUEUED_LOAD_AVERAGE", AVG(AVG_QUEUED_PROVISIONING) AS "QUEUED_PROVISIONING_AVERAGE", AVG(AVG_BLOCKED) AS "BLOCKED_AVERAGE" FROM "SNOWFLAKE"."ACCOUNT_USAGE"."WAREHOUSE_LOAD_HISTORY" GROUP BY 1;' > /tmp/snowflake-warehouse-load-history-metrics.json
$    SNOWSQL_PWD='Replaceme' snowsql -o output_format=json -o remove_comments=true -o header=true -o timing=false -o friendly=false -a <ACCOUNT> -u <USERNAME> -q 'SELECT WAREHOUSE_NAME, avg(CREDITS_USED_COMPUTE) as "CREDITS_USED_COMPUTE_AVERAGE", sum(CREDITS_USED_COMPUTE) as "CREDITS_USED_COMPUTE_SUM", avg(CREDITS_USED_CLOUD_SERVICES) as "CREDITS_USED_CLOUD_SERVICES_AVERAGE", sum(CREDITS_USED_CLOUD_SERVICES) as "CREDITS_USED_CLOUD_SERVICES_SUM", avg(CREDITS_USED) as "CREDITS_USED_AVERAGE", sum(CREDITS_USED) as "CREDITS_USED_SUM" from "SNOWFLAKE"."ACCOUNT_USAGE"."WAREHOUSE_METERING_HISTORY" group by 1;' > /tmp/snowflake-warehouse-metering.json
$    SNOWSQL_PWD='Replaceme' snowsql -o output_format=json -o remove_comments=true -o header=true -o timing=false -o friendly=false -a <ACCOUNT> -u <USERNAME> -q 'SELECT table_name, table_schema, avg(ACTIVE_BYTES) as "ACTIVE_BYTES_AVERAGE", avg(TIME_TRAVEL_BYTES) as "TIME_TRAVEL_BYTES_AVERAGE", avg(FAILSAFE_BYTES) as "FAILSAFE_BYTES_AVERAGE", avg(RETAINED_FOR_CLONE_BYTES) as "RETAINED_FOR_CLONE_BYTES_AVERAGE" from "SNOWFLAKE"."ACCOUNT_USAGE"."TABLE_STORAGE_METRICS" group by 1, 2;' > /tmp/snowflake-table-storage-metrics.json
$    SNOWSQL_PWD='Replaceme' snowsql -o output_format=json -o remove_comments=true -o header=true -o timing=false -o friendly=false -a <ACCOUNT> -u <USERNAME> -q 'SELECT STORAGE_BYTES, STAGE_BYTES, FAILSAFE_BYTES FROM "SNOWFLAKE"."ACCOUNT_USAGE"."STORAGE_USAGE" ORDER BY USAGE_DATE DESC LIMIT 1;' > /tmp/snowflake-storage-usage.json
$    SNOWSQL_PWD='Replaceme' snowsql -o output_format=json -o remove_comments=true -o header=true -o timing=false -o friendly=false -a <ACCOUNT> -u <USERNAME> -q 'SELECT USAGE_DATE, AVG(AVERAGE_STAGE_BYTES) FROM "SNOWFLAKE"."ACCOUNT_USAGE"."STAGE_STORAGE_USAGE_HISTORY" GROUP BY USAGE_DATE;' > /tmp/snowflake-stage-storage-usage-history.json
$    SNOWSQL_PWD='Replaceme' snowsql -o output_format=json -o remove_comments=true -o header=true -o timing=false -o friendly=false -a <ACCOUNT> -u <USERNAME> -q 'SELECT DATABASE_NAME, AVG(CREDITS_USED) AS "CREDITS_USED_AVERAGE", SUM(CREDITS_USED) AS "CREDITS_USED_SUM", AVG(BYTES_TRANSFERRED) AS "BYTES_TRANSFERRED_AVERAGE", SUM(BYTES_TRANSFERRED) AS "BYTES_TRANSFERRED_SUM" FROM "SNOWFLAKE"."ACCOUNT_USAGE"."REPLICATION_USAGE_HISTORY" GROUP BY DATABASE_NAME;' > /tmp/snowflake-replication-usage-history.json
$    SNOWSQL_PWD='Replaceme' snowsql -o output_format=json -o remove_comments=true -o header=true -o timing=false -o friendly=false -a <ACCOUNT> -u <USERNAME> -q 'SELECT QUERY_TYPE, WAREHOUSE_NAME, DATABASE_NAME, SCHEMA_NAME, AVG(EXECUTION_TIME) AS "EXECUTION_TIME_AVERAGE", AVG(COMPILATION_TIME) AS "COMPILATION_TIME_AVERAGE", AVG(BYTES_SCANNED) AS "BYTES_SCANNED_AVERAGE", AVG(BYTES_WRITTEN) AS "BYTES_WRITTEN_AVERAGE", AVG(BYTES_DELETED) AS "BYTES_DELETED_AVERAGE", AVG(BYTES_SPILLED_TO_LOCAL_STORAGE) AS "BYTES_SPILLED_TO_LOCAL_STORAGE_AVERAGE", AVG(BYTES_SPILLED_TO_REMOTE_STORAGE) AS "BYTES_SPILLED_TO_REMOTE_STORAGE_AVERAGE" FROM "SNOWFLAKE"."ACCOUNT_USAGE"."QUERY_HISTORY" GROUP BY QUERY_TYPE, WAREHOUSE_NAME, DATABASE_NAME, SCHEMA_NAME;' > /tmp/snowflake-query-history.json
$    SNOWSQL_PWD='Replaceme' snowsql -o output_format=json -o remove_comments=true -o header=true -o timing=false -o friendly=false -a <ACCOUNT> -u <USERNAME> -q 'SELECT PIPE_NAME, AVG(CREDITS_USED) AS "CREDITS_USED_AVERAGE", SUM(CREDITS_USED) AS "CREDITS_USED_SUM", AVG(BYTES_INSERTED) AS "BYTES_INSERTED_AVERAGE", SUM(BYTES_INSERTED) AS "BYTES_INSERTED_SUM", AVG(FILES_INSERTED) AS "FILES_INSERTED_AVERAGE", SUM(FILES_INSERTED) AS "FILES_INSERTED_SUM" FROM "SNOWFLAKE"."ACCOUNT_USAGE"."PIPE_USAGE_HISTORY" GROUP BY PIPE_NAME;' > /tmp/snowflake-pipe-usage.json
$    SNOWSQL_PWD='Replaceme' snowsql -o output_format=json -o remove_comments=true -o header=true -o timing=false -o friendly=false -a <ACCOUNT> -u <USERNAME> -q 'SELECT QUERY_ID, QUERY_TEXT, (EXECUTION_TIME / 60000) AS EXEC_TIME, WAREHOUSE_NAME, USER_NAME, EXECUTION_STATUS FROM "SNOWFLAKE"."ACCOUNT_USAGE"."QUERY_HISTORY" WHERE EXECUTION_STATUS = '\''SUCCESS'\'' ORDER BY EXECUTION_TIME DESC;' > /tmp/snowflake-longest-queries.json
$    SNOWSQL_PWD='Replaceme' snowsql -o output_format=json -o remove_comments=true -o header=true -o timing=false -o friendly=false -a <ACCOUNT> -u <USERNAME> -q 'SELECT EVENT_ID, EVENT_TIMESTAMP, EVENT_TYPE, REPORTED_CLIENT_TYPE, REPORTED_CLIENT_VERSION, FIRST_AUTHENTICATION_FACTOR, SECOND_AUTHENTICATION_FACTOR, IS_SUCCESS, ERROR_CODE, ERROR_MESSAGE FROM "SNOWFLAKE"."ACCOUNT_USAGE"."LOGIN_HISTORY" WHERE IS_SUCCESS = '\''NO'\'';' > /tmp/snowflake-login-failures.json
$    SNOWSQL_PWD='Replaceme' snowsql -o output_format=json -o remove_comments=true -o header=true -o timing=false -o friendly=false -a <ACCOUNT> -u <USERNAME> -q 'SELECT DATABASE_NAME, AVERAGE_DATABASE_BYTES, AVERAGE_FAILSAFE_BYTES FROM "SNOWFLAKE"."ACCOUNT_USAGE"."DATABASE_STORAGE_USAGE_HISTORY" ORDER BY USAGE_DATE DESC LIMIT 1;' > /tmp/snowflake-database-storage-usage.json
$    SNOWSQL_PWD='Replaceme' snowsql -o output_format=json -o remove_comments=true -o header=true -o timing=false -o friendly=false -a <ACCOUNT> -u <USERNAME> -q 'SELECT SOURCE_CLOUD, SOURCE_REGION, TARGET_CLOUD, TARGET_REGION, TRANSFER_TYPE, AVG(BYTES_TRANSFERRED) AS "BYTES_TRANSFERRED_AVERAGE", SUM(BYTES_TRANSFERRED) AS "BYTES_TRANSFERRED_SUM" FROM "SNOWFLAKE"."ACCOUNT_USAGE"."DATA_TRANSFER_HISTORY" GROUP BY 1, 2, 3, 4, 5;' > /tmp/snowflake-data-transfer-usage.json
$    SNOWSQL_PWD='Replaceme' snowsql -o output_format=json -o remove_comments=true -o header=true -o timing=false -o friendly=false -a <ACCOUNT> -u <USERNAME> -q 'SELECT WAREHOUSE_NAME, SUM(CREDITS_USED) AS TOTAL_CREDITS_USED FROM "SNOWFLAKE"."ACCOUNT_USAGE"."WAREHOUSE_METERING_HISTORY" GROUP BY 1 ORDER BY 2 DESC;' > /tmp/snowflake-credit-usage-by-warehouse.json
$    SNOWSQL_PWD='Replaceme' snowsql -o output_format=json -o remove_comments=true -o header=true -o timing=false -o friendly=false -a <ACCOUNT> -u <USERNAME> -q 'SELECT TABLE_NAME, DATABASE_NAME, SCHEMA_NAME, AVG(CREDITS_USED) AS "CREDITS_USED_AVERAGE", SUM(CREDITS_USED) AS "CREDITS_USED_SUM", AVG(NUM_BYTES_RECLUSTERED) AS "BYTES_RECLUSTERED_AVERAGE", SUM(NUM_BYTES_RECLUSTERED) AS "BYTES_RECLUSTERED_SUM", AVG(NUM_ROWS_RECLUSTERED) AS "ROWS_RECLUSTERED_AVERAGE", SUM(NUM_ROWS_RECLUSTERED) AS "ROWS_RECLUSTERED_SUM" FROM "SNOWFLAKE"."ACCOUNT_USAGE"."AUTOMATIC_CLUSTERING_HISTORY" GROUP BY 1, 2, 3;' > /tmp/snowflake-automatic-clustering.json
$    SNOWSQL_PWD='Replaceme' snowsql -o output_format=json -o remove_comments=true -o header=true -o timing=false -o friendly=false -a <ACCOUNT> -u <USERNAME> -q 'select USER_NAME,EVENT_TYPE,IS_SUCCESS,ERROR_CODE,ERROR_MESSAGE,FIRST_AUTHENTICATION_FACTOR,SECOND_AUTHENTICATION_FACTOR from "SNOWFLAKE"."ACCOUNT_USAGE"."LOGIN_HISTORY";' > /tmp/snowflake-account-details.json

Habilitar la integración de Snowflake con nri-flex

Para configurar la integración de Snowflake, siga estos pasos:

Cree un archivo llamado nri-snowflake-config.yml en el directorio de integración:
bash
```
$touch /etc/newrelic-infra/integrations.d/nri-snowflake-config.yml
```

Agregue el siguiente fragmento a su archivo nri-snowflake-config.yml para permitir que el agente capture datos de Snowflake:

---
    integrations:
    - name: nri-flex
        interval: 30s
        config:
        name: snowflakeAccountMetering
        apis:
            - name: snowflakeAccountMetering
                file: /tmp/snowflake-account-metering.json
    - name: nri-flex
        interval: 30s
        config:
        name: snowflakeWarehouseLoadHistory
        apis:
            - name: snowflakeWarehouseLoadHistory
                file: /tmp/snowflake-warehouse-load-history-metrics.json
    - name: nri-flex
        interval: 30s
        config:
        name: snowflakeWarehouseMetering
        apis:
            - name: snowflakeWarehouseMetering
                file: /tmp/snowflake-warehouse-metering.json
    - name: nri-flex
        interval: 30s
        config:
        name: snowflakeTableStorage
        apis:
            - name: snowflakeTableStorage
                file: /tmp/snowflake-table-storage-metrics.json
    - name: nri-flex
        interval: 30s
        config:
        name: snowflakeStageStorageUsage
        apis:
            - name: snowflakeStageStorageUsage
                file: /tmp/snowflake-stage-storage-usage-history.json
    - name: nri-flex
        interval: 30s
        config:
        name: snowflakeReplicationUsgae
        apis:
            - name: snowflakeReplicationUsgae
                file: /tmp/snowflake-replication-usage-history.json
    - name: nri-flex
        interval: 30s
        config:
        name: snowflakeQueryHistory
        apis:
            - name: snowflakeQueryHistory
                file: /tmp/snowflake-query-history.json
    - name: nri-flex
        interval: 30s
        config:
        name: snowflakePipeUsage
        apis:
            - name: snowflakePipeUsage
                file: /tmp/snowflake-pipe-usage.json
    - name: nri-flex
        interval: 30s
        config:
        name: snowflakeLongestQueries
        apis:
            - name: snowflakeLongestQueries
                file: /tmp/snowflake-longest-queries.json
    - name: nri-flex
        interval: 30s
        config:
        name: snowflakeLoginFailure
        apis:
            - name: snowflakeLoginFailure
                file: /tmp/snowflake-login-failures.json
    - name: nri-flex
        interval: 30s
        config:
        name: snowflakeDatabaseStorageUsage
        apis:
            - name: snowflakeDatabaseStorageUsage
                file: /tmp/snowflake-database-storage-usage.json
    - name: nri-flex
        interval: 30s
        config:
        name: snowflakeDataTransferUsage
        apis:
            - name: snowflakeDataTransferUsage
                file: /tmp/snowflake-data-transfer-usage.json
    - name: nri-flex
        interval: 30s
        config:
        name: snowflakeCreditUsageByWarehouse
        apis:
            - name: snowflakeCreditUsageByWarehouse
                file: /tmp/snowflake-credit-usage-by-warehouse.json
    - name: nri-flex
        interval: 30s
        config:
        name: snowflakeAutomaticClustering
        apis:
            - name: snowflakeAutomaticClustering
                file: /tmp/snowflake-automatic-clustering.json
    - name: nri-flex
        interval: 30s
        config:
        name: snowflakeStorageUsage
        apis:
            - name: snowflakeStorageUsage
                file: /tmp/snowflake-storage-usage.json
    - name: nri-flex
        interval: 30s
        config:
        name: snowflakeAccountDetails
        apis:
            - name: snowflakeAccountDetails
                file: /tmp/snowflake-account-details.json

Reinicie el agente New Relic Infrastructure

Reinicie su agente de infraestructura.

bash

$sudo systemctl restart newrelic-infra.service

En un par de minutos, tu aplicación se enviará métrica a one.newrelic.com.

Encuentra tus datos

Puede elegir nuestra plantilla dashboard prediseñada llamada Snowflake para monitorear su aplicación métrica Snowflake. Siga estos pasos para emplear nuestra plantilla dashboard prediseñada:

Desde one.newrelic.com, vaya a la página + Add data .
Haga clic en Dashboards.
En la barra de búsqueda, escriba Snowflake.
Debería aparecer el dashboard de Snowflake. Haga clic en él para instalarlo.
Su dashboard de Snowflake se considera un dashboard personalizado y se puede encontrar en la UI Dashboards. Para obtener documentos sobre el uso y la edición del panel, consulte nuestra documentación dashboard.
A continuación se muestra una consulta NRQL para comprobar la métrica Snowflake:
```
SELECT * from snowflakeAccountSample
```

¿Que sigue?

Para obtener más información sobre cómo crear una consulta NRQL y generar un dashboard, consulte estos documentos: