pk-node-connector v0.0.8

e6data-node-connector

Introduction

The e6data Connector for node provides an interface for writing node applications that can connect to e6data and perform operations.

To install the Node package, use the command below:

npm install  e6data-node-connector

Prerequisites

• Open Inbound Port 80 in the Engine Cluster.
• Limit access to Port 80 according to your organizational security policy. Public access is not encouraged.
• Access Token generated in the e6data console.

Create a Connection

Use your e6data Email ID as the username and your access token as the password.

const username = '<username>'  // Your e6data Email ID.
const password = '<password>'  // Access Token generated in the e6data console.
const host = '<host>'  // IP address or hostname of the cluster to be used.
const database = '<database>'  // Database to perform the query on.
const port = 80  // Port of the e6data engine.
const catalog_name = '<catalog_name>'

const con  = new Connection(
    host,
    port,
    username,
    password,
    catalog_name,
     database,
    '<cluster_uuid>', // cluster id required for secure connection, optional for insecure connection
    '<secure>',  // secure flag , set it as true for secure connection by default false
  );

Perform a Queries & Get Results

const query = 'SELECT * FROM <TABLE_NAME>'  // Replace with the query.

const cursor = conn.cursor()
const query_id =await  cursor.execute(query);  // The execute function returns a unique query ID, which can be use to abort the query.
const all_records = await cursor.fetchall()

To fetch all the records:

const records = await cursor.fetchall()

To fetch one record:

const record = await cursor.fetchone()

To fetch limited records:

const limit = 500
const records = await cursor.fetchmany(limit)

To fetch all the records in buffer to reduce memory consumption:

const records_iterator = await cursor.fetchAllBuffer()  // Returns generator

To get the execution plan after query execution:

const explain_response = cursor.explain_analyse();
const queryPlanner = JSON.parse(explain_response?.planner);

To abort a running query:

const query_id = '<query_id>'  // query id from execute function response.
cursor.cancel(query_id)

Switch database in an existing connection:

const database = '<new_database_name>'  // Replace with the new database.
const cursor = await conn.cursor(database, catalog_name)

Get Query Time Metrics

const query = 'SELECT * FROM <TABLE_NAME>';

const cursor = conn.cursor();
const query_id = await cursor.execute(query)  // execute function returns query id, can be use for aborting the query.
const all_records = await cursor.fetchall()
const explain_response = await cursor.explain_analyse()
const query_planner = JSON.parse(explainResponse?.planner);

const execution_time = query_planner.total_query_time  // In milliseconds
const queue_time = query_planner.executionQueueingTime  // In milliseconds
const parsing_time = query_planner.parsingTime // In milliseconds
const row_count = query_planner.rowcount

Get Schema - a list of Databases, Tables or Columns

The following code returns a dictionary of all databases, all tables and all columns connected to the cluster currently in use. This function can be used without passing database name to get list of all databases.

const databases = await conn.get_schema_names()  // To get list of databases.


const database = '<database_name>'  // Replace with actual database name.
const tables = await conn.get_tables(database)  // To get list of tables from a database.

const table_name = '<table_name>'  // Replace with actual table name.
const columns = await conn.get_tables(database,table_name)  // To get the list of columns from a table.

Code Hygiene

It is recommended to clear the cursor, close the cursor and close the connection after running a function as a best practice. This enhances performance by clearing old data from memory.

cursor.clear() // Not needed when aborting a query
cursor.close()
conn.close()

Code Example

The following code is an example which combines a few functions described above.

import { Connection, Cursor } from "e6data-node-connector"; 

const username = '<username>'  // Your e6data Email ID.
const password = '<password>'  // Access Token generated in the e6data console.

const host = '<host>'  // IP address or hostname of the cluster to be used.
const database = '<database>'  // Database to perform the query on.
const port = 80  // Port of the e6data engine.

const sql_query = 'SELECT * FROM <TABLE_NAME>'  // Replace with the actual query.

const catalog_name = '<catalog_name>'  // Replace with the actual catalog name.

const con  = new Connection(
    host,
    port,
    username,
    password,
    catalog_name,
     database,
    '<cluster_uuid>', // cluster id required for secure connection, optional for insecure connection
    '<secure>',  // secure flag , set it as true for secure connection by default false
  );

const cursor = conn.cursor()
const query_id = await cursor.execute(sql_query)
const all_records =  await cursor.fetchall() 
const explain_response = await  cursor.explain_analyse()
const planner_result = JSON.parse(explain_response?.planner)
const execution_time = planner_result.total_query_time / 1000  // Converting into seconds.
const row_count = cursor.rowcount
const columns = cursor.description  // Get the columns list

cursor.clear()
cursor.close()
conn.close()