Views

In the side panel (as shown below), user will find option: Views.

Click views, Select data set and set properties (as shown below)

Bizintel360 Data lake SQL Queries

Bizintel360™ Data Lake lets you write sql query with industry standard sql query syntax, functions, aggregations, searches and joins

The easiest way to write SQL Queries is to connect with APIs or with any REST API driven BI App like Tableau, MS Power BI, Oracle OBIEE etc.

Below is the standard REST API for writing sql queries as body in POST Method

Method: POST 

 

Endpoint URL: sub-domain.bizdata360.com:port/_plugins/_sql?format=csv 

Authorization: Basic Authorization. Written in Basic ************ 

Body as JSON Body 

{ 

  "query": "SELECT * FROM bizintel360-movies" 

}

Applicable formats are csv ,json and jdbc ( jdbc also provides response in json with a flat json)

If you use csv, the response will come in csv format and if you use json and jdbc the response will come in json format.

List all the Tables and its columns

List all the Tables available in Bizintel360 Data Lake Hub

SHOW TABLES LIKE %

List all the tables which contain string “order”

SHOW TABLES LIKE orders%

Show all the columns of a table

DESCRIBE TABLES LIKE bizintel360-orders

Read Data

Select * from table_name where column_name=’column_value’

Execution Order

Always execute the sql in the following order

FROM index

WHERE predicates

GROUP BY expressions

HAVING predicates

SELECT expressions

ORDER BY expressions

LIMIT size

Basic SQL Queries

You can write all basic sql queries with SELECT statement with FROM, WHERE, GROUP BY, HAVING, ORDER BY and LIMIT to aggregate and find the data

Retrieve all fields from a table

Select * from table_name

Retrieve only specific fields from a table

select firstname, lastname FROM table_name

Field Aliases

You can use field aliases instead of field names, in order to make field names more readable

select transaction_number as num from transactions

Distinct Clause

select distinct age from transactions

Table Alias

You can use table alias to query across tables

select transactions.name, transactions.age from transactions

select trans.name, trans.age from transactions trans