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 

Complex SQL Queries

With Complex queries in Bzintel360 Data Lake you can do subquery, union, join and minus.

Supported joins are inner joins, cross joins and outer joins.

Inner Join 

 SELECT 

  a.employee_number, a.firstname, a.lastname, 

  e.id, e.name 

FROM transactions a 

JOIN employees e 

ON a.employee_number = e.id

CROSS Join

SELECT 

  a.employee_number, a.firstname, a.lastname, 

  e.id, e.name 

FROM transactions a 

JOIN employees e 

LEFT OUTER Join 

SELECT 

  a.employee_number, a.firstname, a.lastname, 

  e.id, e.name 

FROM transactions a 

LEFT JOIN employees e 

ON a.employee_number = e.id 

Subquery 

SELECT a1.firstname, a1.lastname, a1.balance 

FROM transactions a1 

WHERE a1.employee_number IN ( 

  SELECT a2.employee_number 

  FROM transactions a2 

  WHERE a2.balance > 10000 

) 

From Subquery 

 

SELECT a.f, a.l, a.a 

FROM ( 

  SELECT firstname AS f, lastname AS l, age AS a 

  FROM transactions 

  WHERE age > 21 

) AS a 

Functions 

Bizintel360 Data Lake Support functions like Mathematical Functions, Trigonometric functions, Date & Time functions, String Functions, Aggregate functions and some advanced functions 

Mathematical Functions 

 Below are examples of Mathematical Functions that can be used in Bizintel360 Data Lake 

 

Trigonometric Functions 

Below are examples of Trigonometry Functions that can be used in Bizintel360 Data Lake 

Date & Time Functions 

Below are examples of Date & Time Functions that can be used in Bizintel360 Data Lake 

String Functions 

Below are examples of String Functions that can be used in Bizintel360 Data Lake 

Aggregate Functions

Below are examples of Aggregate Functions that can be used in Bizintel360 Data Lake 

Some Advanced Functions 

Below are examples of some extra Advanced Functions that can be used in Bizintel360 Data Lake 

Aggregation Functions 

 Aggregation functions use GROUP BY to group the dataset values into subsets. You can write sql to aggregate data by COUNT, SUM, AVG, MIN, and MAX 

Below are multiple examples to use the aggregation functions 

SELECT category, sum(sales) FROM orders GROUP BY category 

SELECT category, sum(sales) FROM orders GROUP BY 1 

SELECT abs(account_number), sum(sales) FROM orders GROUP BY abs(account_number) 

SELECT category, sum(sales) FROM orders GROUP BY category 

SELECT category, sum(sales) * 2 as sum2 FROM orders GROUP BY category 

SELECT category, sum(sales * 2) as sum2 FROM orders GROUP BY category 

 # Having with Group By  

SELECT category, sum(sales) 

FROM orders 

GROUP BY category 

HAVING sum(sales) > 100; 

 # Having with Group By using Alias 

 SELECT category, sum(sales) AS s 

FROM orders 

GROUP BY category 

HAVING s > 100; 

  

#Having without Group By 

 SELECT 'Total of sales > 100' 

FROM orders 

HAVING sum(sales) > 100;