Snowflake to Elasticsearch

Data Migration Using Logstash

On one hand, we have Elasticsearch, a cloud or on-premise NoSQL database, and Snowflake, a cloud-based data warehouse.

Elasticsearch and Snowflake possess unique infrastructures, purposes, and applications. Elasticsearch excels in searching and analyzing unstructured or semi-structured data, such as log files, and offers robust security and observability features. It facilitates real-time system performance monitoring and log visualization, aiding in issue detection and resolution. In contrast, Snowflake functions as a specialized cloud data warehouse for structured data, making it an ideal choice for business intelligence and reporting.

Migrating data from Snowflake to Elasticsearch presents a unique challenge. This article will explore the use of Logstash as a solution for transferring data from Snowflake to Elasticsearch.

Logstash is an open-source pipeline developed by Elastic NV and is a fundamental component of the ELK (Elasticsearch, Logstash, and Kibana) Stack. Logstash serves as a server-side data processing pipeline, enabling users to import, transform, and export log and event data from various sources into Elasticsearch.

A configuration file plays a vital role in data migration procedures. You create pipeline configuration files within the ‘config’ folder to define Logstash’s processing stages. Logstash loads only ‘.conf’ files in the ‘config’ directory, disregarding others. The basic configuration includes input, filter, and output plugins. Input plugins read source events, filter plugins process events, and output plugins send data to specific destinations.

Refer to the following Logstash configuration template for migrating data from Snowflake to Elasticsearch.

In the below configuration template, we used the JDBC plugin within the input section and the Elasticsearch plugin within the output section.

Migration Procedure :

• Begin by installing the Snowflake JDBC driver and saving it within the Logstash folder in your preferred location.
• Next, create a configuration (.conf) file and save it within the ‘config’ folder located in the Logstash directory.

For the config file, you can refer to the below template.

input {
  jdbc {

    # path to third party driver library
    # replace it with path where you placed the driver
    jdbc_driver_library => "/path/to/snowflake/driver"

    # class to load
    jdbc_driver_class => "com.snowflake.client.jdbc.SnowflakeDriver"

    # Replace JDBC connection string with your actual database details.
    jdbc_connection_string => "jdbc:snowflake://<account_locator>.<cloud-region>.<cloud>.snowflakecomputing.com/?db=<database_name>&warehouse=<warehouse_name>"
    
    # snowflake credentials
    # replace it with your own credentials
    jdbc_user => "<username>" # snowflake account username
    jdbc_password => "<passwd>" # snowflake account password
    schedule => "* * * * *"
    statement => "SELECT * FROM DETAILS LIMIT 10;" # SQL statement
    # Replace the above statement with you own query.
  }
}
filter {
# filter plugin is empty
}
output
{
     elasticsearch
     {
        # elastic credentials
        # replace it with your credentials  
        hosts => ["https://<host/IP address>:<port>"]
        user => "<username>" # elastic username
        password => "<passwd>" # elastic password

        data_stream => "true"
        data_stream_type => "logs"
        data_stream_dataset => "snowflake"
        data_stream_namespace => "data"
        # output DataStream will be logs-snowflake-data
        }
}

• Once the Snowflake JDBC driver is placed in the Logstash folder, and the configuration file is saved in the ‘config’ folder, you are ready to start Logstash.
• For Windows OS, open the command prompt (cmd) from the ‘bin’ folder and execute the following command:

# replace the conf_file_name
logstash.bat -f .\config\<conf_file_name>.conf

• Similarly, for other operating systems, execute the same command, but ensure that you run it from the ‘bin’ folder.

# replace the conf_file_name
bin/logstash -f ./config/<conf_file_name>.conf

Troubleshooting — Error

The issue I faced while performing the migration on Windows OS is “Unable to configure the plugins: cannot determine time zone from nil.” The solution that worked for me was to change the time zone of my system to UTC (UTC+1 or UTC-1).

For a better solution to the above issue, you can refer to the below links posted in the Elastic discussion.

Logstash doesn't work in Windows correctly Please help!

I get error Unable to configure plugins: (ArgumentError) Cannot determine timezone from nil