Skip to content

Connect to Snowflake¤

Introduction¤

eccenca Corporate Memory is a semantic data management platform that allows organizations to store, manage, and interconnect vast amounts of structured data.

Snowflake is a cloud-based data warehousing solution that provides a scalable and flexible platform for data storage and analysis.

The eccenca Corporate Memory platform can connect to Snowflake to take advantage of Snowflake’s scalability and flexibility for cloud data warehousing. This connection allows organizations to store and manage large amounts of data in Snowflake while using eccenca Corporate Memory to link and interconnect with other data sources, such as databases and cloud applications, to form a comprehensive and unified view of all data assets.

By integrating Snowflake with eccenca Corporate Memory, organizations can achieve a centralized and unified data management system that allows them to gain a complete and accurate view of all their data assets. This integration enables organizations to make informed decisions, improve their business processes, and drive growth and innovation by leveraging their data assets.

This tutorial contains the following step-by-step instructions to connect the Snowflake data-warehouse with eccenca corporate memory:

Sample material¤

The following material is used in this tutorial, you should download the files and have them at hand throughout the tutorial:

1. Create a database in Snowflake¤

  • Login to Snowflake enter the username and password, then click on Sign in.

    image

  • Click on Database on the left side of the page.

    image

  • Click on +Database on the right side of the page.

    image

  • Type the database name Product, then click on Create.

    image

  • Click on database product, then click on +Schema on the right side of the page.

    image

  • Type the schema name products_vocabulary and click on Create.

    image

  • Click on scheme products_vocabulary on the left side of the page then click on Create on the right side of the page, then click on Table, then select then Standard.

    image

  • Click on schema name products_vocabulary on the left side of the page and type the sql query for creating a table in the center, then click on Run on the right side of the page.

    You can create the PRODUCT table with the following SQL query:

    CREATE TABLE product(
        product_id        VARCHAR(12) NOT NULL PRIMARY KEY
        , product_name    VARCHAR(50) NOT NULL
        , height          INTEGER     NOT NULL
        , width           INTEGER     NOT NULL
        , depth           INTEGER     NOT NULL
        , weigth          INTEGER     NOT NULL
        , product_manager VARCHAR(50) NOT NULL
        , price           VARCHAR(10) NOT NULL
    ) ;
    
  • Type or copy the SQL query for creating a database in the table that is created, then click on Run.

    image

In the Worksheets view (you might need to create a new worksheet), select the product database, the products_vocabulary schema and finally the product table. Here you can populate some test data with the following SQL query:

INSERT query

image

Step Result

The database has been populated as shown below.

image

2. Create a project in eccenca Corporate Memory¤

  • Click on Create on the right side of the page.

    image

  • Click on Project, then click on Add.

    image

  • Type the project name product in the title field, then click on Create.

    image

  • Click on Create on the right side of the page.

    image

  • Click on Snowflake SQL endpoint, then click on Add.

    image

  • Type the name Product table (JDBC) in the label field.

  • In the CONNECTION section populate the required details.

    image

    • Replace <orgname> and <account_name> in the Account URL hostname field. kiaouyb-fe21477.snowflakecomputing.com is the URL for the snowflake account you want to connect to. The number WTXSZXM-FS77078 is the organization and account number you will get from Snowflake as shown below.

      image

    • Type your User and Password.

    • Select your Database, product in this case.

    • Select your Schema, products_vocabulary in this case

  • In the READ section specify Source query as

    SELECT * from product
    

    image

  • Select the Query strategy as Execute the given source query. No paging or virtual query.

    image

  • Select the Write strategy as An exception will be thrown, if the table already exists.

    image

  • Then click on Create.

Step Result

JDBC endpoint is created and data is transferred from Snowflake to eccenca Corporate Memory.

image

3. Create a transformation to build mapping rules¤

  • Click on Create on the right side of the page.

    image

  • Click on Transform on the left side of the page, then on Transform in the centre of the page, then click on Add.

    image

  • Type the name product in the Label field, in the INPUT TASK Dataset select Product Table (JDBC) and in the Type field select table.

    image

    image

  • In the Output dataset field select product graph, then click on Create .

    image

  • Click on Mapping, then click on Edit.

    image

  • For the target entity select Product (pv:Product).

    image

  • Click on create custom pattern.

    image

  • Type the URI pattern as <http://id.company.org/product/jdbc>. You can use either company.org or company.com as per your requirement. Then type the label name as product and then click on save.

    image

  • Click on +Icon, then select the Add value mapping.

    image

  • Select the target property according to transformation requirements, for example name, id, etc., then select the value path according to the target property as the product name, product id etc. This step will help in mapping the data from the source to the target property.

    image

  • Type the label name product name, then click on Save.

    image

    Step Result

    Mapping rule is created successfully.

    image

    Note

    We have the suggestion option as well; click on the +Icon and select the Suggestion mapping.

    image

    Step Result

    Suggestion appears as below can select as per the requirement.

    image

    Note

    Suggestions generated are based on vocabulary which describes the data in the CSV files: products_vocabulary.nt

  • Tick the box to select the suggestions to be added, then click on Add.

image

4. Create a knowledge graph¤

  • Click on Create on the right side of the page.

    image

  • Select Knowledge Graph, then click on Add.

    image

  • Select the target project from the drop down menu as product.

    image

  • Type product graph in the label field, then enter the graph URI in the Graph field, then click on Create.

    image

Step Result

Graph is created successfully.

image

Comments