Zetaris Support - Knowledge Base

How do I connect to test data sources?

To allow the testing of the Cloud Data Fabric a number of data sets have been created.

Three different databases in Azure and AWS, along with BLOB and s3 bucket files have been created.

Access to these requires the definition of the metadata within the cloud data fabric which is achieved by:

Creating the data source, and Registering the data source for databases.
Creating Databases for s3 and BLOB files

Once the data sources have been defined the data can then be queried using the SQL editor.

The following commands should be executed within the SQL Editor on the Schema Store View page of the GUI to define and register the desired data sources;

AWS Redshift commands

AWS_RDSH Datasource:

CREATE DATASOURCE AWS_RDSH DESCRIBE BY "AWS_RDSH" OPTIONS (

    jdbcdriver "com.amazon.redshift.jdbc.Driver",

    jdbcurl "jdbc:postgresql://zetaris.cyzoanxzdpje.ap-southeast-2.redshift.amazonaws.com:5439/zetredshift",

    username "aws_test_data" ,

    password "Tcph_Data_1"

);

 

REGISTER DATASOURCE TABLES FROM AWS_RDSH;

AWS_ORCL Datasource:

CREATE DATASOURCE AWS_ORCL DESCRIBE BY "AWS_ORCL" OPTIONS (

    jdbcdriver "com.amazon.redshift.jdbc.Driver",

    jdbcurl "jdbc:postgresql://zetaris.cyzoanxzdpje.ap-southeast-2.redshift.amazonaws.com:5439/zetredshift",

    username "aws_test_data" ,

    password "Tcph_Data_1"

);

 

REGISTER DATASOURCE TABLES FROM AWS_ORCL;

Azure SQL commands

AZURE_MSSQL Datasource:

CREATE DATASOURCE AZURE_MSSQL DESCRIBE BY "AZURE_MSSQL" OPTIONS (

    jdbcdriver "com.microsoft.sqlserver.jdbc.SQLServerDriver",

    jdbcurl "jdbc:sqlserver://microsoftsqlserver.database.windows.net:1433 ",

    databaseName "DemoData",

    username "adminnew" ,

    password "WarehouseZet123",

    schema "tpch1"

);

 

 

REGISTER DATASOURCE TABLES FROM AZURE_MSSQL;

AZURE_POSTGRES Datasource:

CREATE DATASOURCE AZURE_POSTGRES DESCRIBE BY "sports sample data" OPTIONS (

    jdbcdriver "org.postgresql.Driver",

    jdbcurl "jdbc:postgresql://zetaris-postgresql-testdata-server.postgres.database.azure.com:5432/postgres",

    username "zetaris@zetaris-postgresql-testdata-server" ,

    password "Z7e3T7a2R4i1S5!@#",

    schema "public"

);

 

REGISTER DATASOURCE TABLES FROM AZURE_POSTGRES;

Azure BLOB commands

TPCH_AZBLB Databases:

CREATE LIGHTNING DATABASE TPCH_AZBLB DESCRIBE BY "TPCH Data set in Azure blob";

 

CREATE LIGHTNING FILESTORE TABLE customer FROM TPCH_AZBLB FORMAT CSV OPTIONS (

    PATH "wasb://zettest-storage-container@zettesstorage.blob.core.windows.net/customer.csv",

    inferSchema "true",

    fs.azure.account.key.zettesstorage.blob.core.windows.net "bHLzau36KlZ6cYnSrvPzSJVniBDtu819nHTR/+hRyDZEVScQ3wuesst9P5/I7vqG+4czeimuHSrPe2ZtK+b+BQ==");

 

 

 

CREATE LIGHTNING FILESTORE TABLE line FROM TPCH_AZBLB FORMAT CSV OPTIONS (

    PATH "wasb://zettest-storage-container@zettesstorage.blob.core.windows.net/line.csv",

    inferSchema "true",

    fs.azure.account.key.zettesstorage.blob.core.windows.net "bHLzau36KlZ6cYnSrvPzSJVniBDtu819nHTR/+hRyDZEVScQ3wuesst9P5/I7vqG+4czeimuHSrPe2ZtK+b+BQ=="

);

CREATE LIGHTNING FILESTORE TABLE nation FROM TPCH_AZBLB FORMAT CSV OPTIONS (

    PATH "wasb://zettest-storage-container@zettesstorage.blob.core.windows.net/nation.csv",

    inferSchema "true",

    fs.azure.account.key.zettesstorage.blob.core.windows.net "bHLzau36KlZ6cYnSrvPzSJVniBDtu819nHTR/+hRyDZEVScQ3wuesst9P5/I7vqG+4czeimuHSrPe2ZtK+b+BQ=="

);

 

 

 

CREATE LIGHTNING FILESTORE TABLE orders FROM TPCH_AZBLB FORMAT CSV OPTIONS (

    PATH "wasb://zettest-storage-container@zettesstorage.blob.core.windows.net/orders.csv",

    inferSchema "true",

    fs.azure.account.key.zettesstorage.blob.core.windows.net "bHLzau36KlZ6cYnSrvPzSJVniBDtu819nHTR/+hRyDZEVScQ3wuesst9P5/I7vqG+4czeimuHSrPe2ZtK+b+BQ=="

);

CREATE LIGHTNING FILESTORE TABLE partsupp FROM TPCH_AZBLB FORMAT CSV OPTIONS (

    PATH "wasb://zettest-storage-container@zettesstorage.blob.core.windows.net/partsupp.csv",

    inferSchema "true",

    fs.azure.account.key.zettesstorage.blob.core.windows.net "bHLzau36KlZ6cYnSrvPzSJVniBDtu819nHTR/+hRyDZEVScQ3wuesst9P5/I7vqG+4czeimuHSrPe2ZtK+b+BQ=="

);

CREATE LIGHTNING FILESTORE TABLE part FROM TPCH_AZBLB FORMAT CSV OPTIONS (

    PATH "wasb://zettest-storage-container@zettesstorage.blob.core.windows.net/part.csv",

    inferSchema "true",

    fs.azure.account.key.zettesstorage.blob.core.windows.net "bHLzau36KlZ6cYnSrvPzSJVniBDtu819nHTR/+hRyDZEVScQ3wuesst9P5/I7vqG+4czeimuHSrPe2ZtK+b+BQ=="

);

CREATE LIGHTNING FILESTORE TABLE region FROM TPCH_AZBLB FORMAT CSV OPTIONS (

    PATH "wasb://zettest-storage-container@zettesstorage.blob.core.windows.net/region.csv",

    inferSchema "true",

    fs.azure.account.key.zettesstorage.blob.core.windows.net "bHLzau36KlZ6cYnSrvPzSJVniBDtu819nHTR/+hRyDZEVScQ3wuesst9P5/I7vqG+4czeimuHSrPe2ZtK+b+BQ=="

);

CREATE LIGHTNING FILESTORE TABLE supplier FROM TPCH_AZBLB FORMAT CSV OPTIONS (

    PATH "wasb://zettest-storage-container@zettesstorage.blob.core.windows.net/supplier.csv",

    inferSchema "true",

    fs.azure.account.key.zettesstorage.blob.core.windows.net "bHLzau36KlZ6cYnSrvPzSJVniBDtu819nHTR/+hRyDZEVScQ3wuesst9P5/I7vqG+4czeimuHSrPe2ZtK+b+BQ=="

);

AWS s3 commands

TPCH_S3 Databases:

DROP LIGHTNING DATABASE TPCH_S3;

CREATE LIGHTNING DATABASE TPCH_S3 DESCRIBE BY "TPCH Data set in aws s3";

CREATE LIGHTNING FILESTORE TABLE customer FROM TPCH_S3 FORMAT CSV OPTIONS (

PATH "s3n://zetaris-lightning-test/csv-data/tpc-h/customer.csv",

inferSchema "true",

header "true",

AWSACCESSKEYID "AKIAUDI43WQVVSVX6YWZ",

AWSSECRETACCESSKEY "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"

);

CREATE LIGHTNING FILESTORE TABLE line FROM TPCH_S3 FORMAT CSV OPTIONS (

PATH "s3n://zetaris-lightning-test/csv-data/tpc-h/line.csv",

inferSchema "true",

header "true",

AWSACCESSKEYID "AKIAUDI43WQVVSVX6YWZ",

AWSSECRETACCESSKEY "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"

);

CREATE LIGHTNING FILESTORE TABLE nation FROM TPCH_S3 FORMAT CSV OPTIONS (

PATH "s3n://zetaris-lightning-test/csv-data/tpc-h/nation.csv",

header "true",

inferSchema "true",

AWSACCESSKEYID "AKIAUDI43WQVVSVX6YWZ",

AWSSECRETACCESSKEY "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"

);

CREATE LIGHTNING FILESTORE TABLE orders FROM TPCH_S3 FORMAT CSV OPTIONS (

PATH "s3n://zetaris-lightning-test/csv-data/tpc-h/orders.csv",

header "true",

inferSchema "true",

AWSACCESSKEYID "AKIAUDI43WQVVSVX6YWZ",

AWSSECRETACCESSKEY "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"

);

CREATE LIGHTNING FILESTORE TABLE partsupp FROM TPCH_S3 FORMAT CSV OPTIONS (

PATH "s3n://zetaris-lightning-test/csv-data/tpc-h/partsupp.csv",

header "true",

inferSchema "true",

AWSACCESSKEYID "AKIAUDI43WQVVSVX6YWZ",

AWSSECRETACCESSKEY "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"

);

CREATE LIGHTNING FILESTORE TABLE part FROM TPCH_S3 FORMAT CSV OPTIONS (

PATH "s3n://zetaris-lightning-test/csv-data/tpc-h/part.csv",

header "true",

inferSchema "true",

AWSACCESSKEYID "AKIAUDI43WQVVSVX6YWZ",

AWSSECRETACCESSKEY "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"

);

CREATE LIGHTNING FILESTORE TABLE region FROM TPCH_S3 FORMAT CSV OPTIONS (

PATH "s3n://zetaris-lightning-test/csv-data/tpc-h/region.csv",

header "true",

inferSchema "true",

AWSACCESSKEYID "AKIAUDI43WQVVSVX6YWZ",

AWSSECRETACCESSKEY "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"

);

CREATE LIGHTNING FILESTORE TABLE supplier FROM TPCH_S3 FORMAT CSV OPTIONS (

PATH "s3n://zetaris-lightning-test/csv-data/tpc-h/supplier.csv",

header "true",

inferSchema "true",

AWSACCESSKEYID "AKIAUDI43WQVVSVX6YWZ",

AWSSECRETACCESSKEY "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"

);