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
| Description | Operator |
| = | Equal To |
| <> | Not Equal To |
| > | Greater Than |
| < | Less Than |
| >= | Greater than or equal to |
| <= | Less than or equal to |
| IN | You can Specify multiple OR operators |
| BETWEEN | Searched the records between range of numeric and date field |
| LIKE | Search for the keyword or string |
| IS NULL | It checks if field value is null |
| IS NOT NULL | It checks if the field value is not null |
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
| Function | Example |
| abs | SELECT abs(0.5) FROM bizintel360-tablename LIMIT 1 |
| add | SELECT add(1, 5) FROM bizintel360-tablename LIMIT 1 |
| cbrt | SELECT cbrt(0.5) FROM bizintel360-tablename LIMIT 1 |
| ceil | SELECT ceil(0.5) FROM bizintel360-tablename LIMIT 1 |
| conv | SELECT CONV(’12’, 10, 16), CONV(‘2C’, 16, 10), CONV(12, 10, 2), CONV(1111, 2, 10) FROM bizintel360-tablename LIMIT 1 |
| crc32 | SELECT crc32(‘MySQL’) FROM bizintel360-tablename LIMIT 1 |
| divide | SELECT divide(1, 0.5) FROM bizintel360-tablename LIMIT 1 |
| e | SELECT e() FROM bizintel360-tablename LIMIT 1 |
| exp | SELECT exp(0.5) FROM bizintel360-tablename LIMIT 1 |
| expm1 | SELECT expm1(0.5) FROM bizintel360-tablename LIMIT 1 |
| floor | SELECT floor(0.5) AS Rounded_Down FROM bizintel360-tablename LIMIT 1 |
| ln | SELECT ln FROM bizintel360-tablename LIMIT 1 |
| log | SELECT log FROM bizintel360-tablename LIMIT 1 |
| log2 | SELECT log2 FROM bizintel360-tablename LIMIT 1 |
| log10 | SELECT log10 FROM bizintel360-tablename LIMIT 1 |
| mod | SELECT modulus(2, 3) FROM bizintel360-tablename LIMIT 1 |
| multiply | SELECT multiply(2, 3) FROM bizintel360-tablename LIMIT 1 |
| pi | SELECT pi() FROM bizintel360-tablename LIMIT 1 |
| pow | SELECT pow(2, 3) FROM bizintel360-tablename LIMIT 1 |
| power | SELECT power(2, 3) FROM bizintel360-tablename LIMIT 1 |
| rand | SELECT rand(0.5) FROM bizintel360-tablename LIMIT 1 |
| rint | SELECT rint(1.5) FROM bizintel360-tablename LIMIT 1 |
| round | SELECT round(1.5) FROM bizintel360-tablename LIMIT 1 |
| sign | SELECT sign(1.5) FROM bizintel360-tablename LIMIT 1 |
| signum | SELECT signum(0.5) FROM bizintel360-tablename LIMIT 1 |
| sqrt | SELECT sqrt(0.5) FROM bizintel360-tablename LIMIT 1 |
| strcmp | SELECT strcmp(‘hello’, ‘hello’) FROM bizintel360-tablename LIMIT 1 |
| subtract | SELECT subtract(3, 2) FROM bizintel360-tablename LIMIT 1 |
| truncate | SELECT truncate(56.78, 1) FROM bizintel360-tablename LIMIT 1 |
| / | SELECT 1 / 100 FROM bizintel360-tablename LIMIT 1 |
| % | SELECT 1 % 100 FROM bizintel360-tablename LIMIT 1 |
Trigonometric Functions
Below are examples of Trigonometry Functions that can be used in Bizintel360 Data Lake
| Function | Example |
| acos | SELECT acos(0.5) FROM bizintel360-tablename LIMIT 1 |
| asin | SELECT asin(0.5) FROM bizintel360-tablename LIMIT 1 |
| atan | SELECT atan(0.5) FROM bizintel360-tablename LIMIT 1 |
| atan2 | SELECT atan2(1, 0.5) FROM bizintel360-tablename LIMIT 1 |
| cos | SELECT cos(0.5) FROM bizintel360-tablename LIMIT 1 |
| cosh | SELECT cosh(0.5) FROM bizintel360-tablename LIMIT 1 |
| cot | SELECT cot(0.5) FROM bizintel360-tablename LIMIT 1 |
| degrees | SELECT degrees(0.5) FROM bizintel360-tablename LIMIT 1 |
| radians | SELECT radians(0.5) FROM bizintel360-tablename LIMIT 1 |
| sin | SELECT sin(0.5) FROM bizintel360-tablename LIMIT 1 |
| sinh | SELECT sinh(0.5) FROM bizintel360-tablename LIMIT 1 |
| tan | SELECT tan(0.5) FROM bizintel360-tablename LIMIT 1 |
Date & Time Functions
Below are examples of Date & Time Functions that can be used in Bizintel360 Data Lake
| Function | Example |
| adddate | SELECT adddate(date(‘2020-08-26’), INTERVAL 1 hour) FROM bizintel360-tablename LIMIT 1 |
| curdate | SELECT curdate() FROM bizintel360-tablename LIMIT 1 |
| date | SELECT date() FROM bizintel360-tablename LIMIT 1 |
| date_format | SELECT date_format(date, ‘Y’) FROM bizintel360-tablename LIMIT 1 |
| date_sub | SELECT date_sub(date(‘2008-01-02’), INTERVAL 31 day) FROM bizintel360-tablename LIMIT 1 |
| dayofmonth | SELECT dayofmonth(date) FROM bizintel360-tablename LIMIT 1 |
| dayname | SELECT dayname(date(‘2020-08-26’)) FROM bizintel360-tablename LIMIT 1 |
| dayofyear | SELECT dayofyear(date(‘2020-08-26’)) FROM bizintel360-tablename LIMIT 1 |
| dayofweek | SELECT dayofweek(date(‘2020-08-26’)) FROM bizintel360-tablename LIMIT 1 |
| from_days | SELECT from_days FROM bizintel360-tablename LIMIT 1 |
| hour | SELECT hour((time ’01:02:03′)) FROM bizintel360-tablename LIMIT 1 |
| maketime | SELECT maketime(1, 2, 3) FROM bizintel360-tablename LIMIT 1 |
| microsecond | SELECT microsecond((time ’01:02:03.123456′)) FROM bizintel360-tablename LIMIT 1 |
| minute | SELECT minute((time ’01:02:03′)) FROM bizintel360-tablename LIMIT 1 |
| month | SELECT month(date) FROM bizintel360-tablename |
| monthname | SELECT monthname(date) FROM bizintel360-tablename |
| now | SELECT now() FROM bizintel360-tablename LIMIT 1 |
| quarter | SELECT quarter(date(‘2020-08-26’)) FROM bizintel360-tablename LIMIT 1 |
| second | SELECT second((time ’01:02:03′)) FROM bizintel360-tablename LIMIT 1 |
| subdate | SELECT subdate(date(‘2008-01-02’), INTERVAL 31 day) FROM bizintel360-tablename LIMIT 1 |
| time | SELECT time(’13:49:00′) FROM bizintel360-tablename LIMIT 1 |
| time_to_sec | SELECT time_to_sec(time ’22:23:00′) FROM bizintel360-tablename LIMIT 1 |
| timestamp | SELECT timestamp(date) FROM bizintel360-tablename LIMIT 1 |
| to_days | SELECT to_days(date ‘2008-10-07’) FROM bizintel360-tablename LIMIT 1 |
| week | SELECT week(date(‘2008-02-20’)) FROM bizintel360-tablename LIMIT 1 |
| year | SELECT year(date) FROM bizintel360-tablename LIMIT 1 |
String Functions
Below are examples of String Functions that can be used in Bizintel360 Data Lake
| Function | Example |
| ascii | SELECT ascii(name.keyword) FROM bizintel360-tablename LIMIT 1 |
| concat | SELECT concat(‘hello’, ‘world’) FROM bizintel360-tablename LIMIT 1 |
| concat_ws | SELECT concat_ws(“-“, “Tutorial”, “is”, “fun!”) FROM bizintel360-tablename LIMIT 1 |
| left | SELECT left(‘hello’, 2) FROM bizintel360-tablename LIMIT 1 |
| length | SELECT length(‘hello’) FROM bizintel360-tablename LIMIT 1 |
| locate | SELECT locate(‘o’, ‘hello’) FROM bizintel360-tablename LIMIT 1 |
| SELECT locate(‘l’, ‘hello’, 3) FROM bizintel360-tablename LIMIT 1 | |
| replace | SELECT replace(‘hello’, ‘l’, ‘x’) FROM bizintel360-tablename LIMIT 1 |
| right | SELECT right(‘hello’, 1) FROM bizintel360-tablename LIMIT 1 |
| rtrim | SELECT rtrim(name.keyword) FROM bizintel360-tablename LIMIT 1 |
| substring | SELECT substring(name.keyword, 2,5) FROM bizintel360-tablename LIMIT 1 |
| trim | SELECT trim(‘ hello’) FROM bizintel360-tablename LIMIT 1 |
| upper | SELECT upper(‘helloworld’) FROM bizintel360-tablename LIMIT 1 |
Aggregate Functions
Below are examples of Aggregate Functions that can be used in Bizintel360 Data Lake
| Function | Example |
| avg | SELECT avg(2, 3) FROM bizintel360-tablename LIMIT 1 |
| count | SELECT count(date) FROM bizintel360-tablename LIMIT 1 |
| min | SELECT min(2, 3) FROM bizintel360-tablename LIMIT 1 |
| show | SHOW TABLES LIKE bizintel360-tablename |
Some Advanced Functions
Below are examples of some extra Advanced Functions that can be used in Bizintel360 Data Lake
| Function | Example |
| if | SELECT if(false, 0, 1) FROM my-index LIMIT 1 |
| SELECT if(true, 0, 1) FROM my-index LIMIT 1 | |
| ifnull | SELECT ifnull(‘hello’, 1) FROM my-index LIMIT 1 |
| SELECT ifnull(null, 1) FROM my-index LIMIT 1 | |
|
isnull |
SELECT isnull(null) FROM my-index LIMIT 1 |
| SELECT isnull FROM my-index LIMIT 1 |
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;
