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 

OR 

select trans.name, trans.age from transactions trans 

Filter/Where Conditions 

 You can put all sorts of arithmetic conditions to filter the records using WHERE clause 

select transaction_number from transactions where transaction.id=12 

Group By Fields 

select age from transactions GROUP BY age 

Group by Field Alias 

select transaction_number as num from transactions GROUP BY num

Group by Scalar Functions 

select ABS (age) AS age from transactions GROUP BY ABS (age) 

Having Clause 

You can use aggregations in HAVING as COUNT, SUM, AVG, MIN, and MAX 

SELECT age, MAX (balance) 

FROM transactions 

GROUP BY age HAVING MIN (balance) > 10000

Order By 

Use Order by to sort the results in desired order 

SELECT transaction_number 

FROM transactions 

ORDER BY transaction_number DESC 

Order By with NOT NULL 

SELECT employee 

FROM transactions 

ORDER BY employee IS NOT NULL 

LIMIT 

You can specify the maximum number of records that you want to retrieve  

SELECT transaction_number 

FROM transactions 

ORDER BY transaction_number LIMIT 1