Apache Tajo SQL Statements - Apache Tajo

How to create Table Statement in Apache Tajo?

Before moving to create a table, a text file “students.csv” is created in Tajo installation directory path as follows −

students.csv

Id

Name

Address

Age

Marks

1

Adam

23 New Street

21

90

2

Amit

12 Old Street

13

95

3

Bob

10 Cross Street

12

80

4

David

15 Express Avenue

12

85

5

Esha

20 Garden Street

13

50

6

Ganga

25 North Street

12

55

7

Jack

2 Park Street

12

60

8

Leena

24 South Street

12

70

9

Mary

5 West Street

12

75

10

Peter

16 Park Avenue

12

95

After the file has been created, move to the terminal and start the Tajo server and shell one by one.

How to create a Database in Apache Tajo?

Create a new database using the following command −

Query

Connect to the database “sampledb” which is now created.

Then, create a table in “sampledb” as follows −

Query

Result

The above query will generate the following result.

Here, the external table is created. Now, enter the file location. Use hdfs instead of file, to assign the table from hdfs.

Next, the “students.csv” file contains comma separated values. The text.delimiter field is assigned with ‘,’.

“mytable” is created successfully in “sampledb”.

How to show Table in Apache Tajo?

To show tables in Tajo, use the following query.

Query

Result

The above query will generate the following result.

How to list table in Apache Tajo?

To fetch all the records in the table, type the following query −

Query

Result

The above query will generate the following result.

List Table

How to insert Table Statement in Apache Tajo?

Tajo uses the following syntax to insert records in table.

Syntax

Tajo’s insert statement is similar to the INSERT INTO SELECT statement of SQL.

Query

A table iscreated to overwrite table data of an existing table.

Result

The above query will generate the following result.

How to insert Records in Apache Tajo?

To insert records in the “test” table, type the following query.

Query

Result

The above query will generate the following result.

Here, “mytable" records overwrite the “test” table. If you don’t want to create the “test” table, then straight away assign the physical path location as mentioned in an alternative option for insert query.

How to fetch records in Apache Tajo?

Use the following query to list out all the records in the “test” table −

Query

Result

The above query will generate the following result.

Fetch Records

This statement is used to add, remove or modify columns of an existing table.

To rename the table use the following syntax −

Query

Result

The above query will generate the following result.

To check the changed table name, use the following query.

Now the table “test” is changed to “students” table.

How to Add Column in Apache Tajo?

To insert new column in the “students” table, type the following syntax −

Query

Result

The above query will generate the following result.

How to set Property in Apache Tajo?

This property is used to change the table’s property.

Query

Here, compression type and codec properties are assigned.

To change the text delimiter property, use the following −

Query

Result

The above query will generate the following result.

The above result shows that the table’s properties are changed using the “SET” property.

How to select Statement in Apache Tajo?

The SELECT statement is used to select data from a database.

The syntax for the Select statement is as follows −

What is Where Clause in Apache Tajo?

The Where clause is used to filter records from the table.

Query

Result

The above query will generate the following result.

Where Clause

The query returns the records of those students whose id is greater than 5.

Query

Result

The above query will generate the following result.

The result filters Peter’s records only.

What is Distinct Clause in Apache Tajo?

A table column may contain duplicate values. The DISTINCT keyword can be used to return only distinct (different) values.

Syntax

Query

Result

The above query will generate the following result.

The query returns the distinct age of students from mytable.

What is Group By Clause in Apache Tajo?

The GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups.

Syntax

Query

Result

The above query will generate the following result.

Here, the “mytable" column has two types of ages — 12 and 13. Now the query groups the records by age and produces the sum of marks for the corresponding ages of students.

What is Having Clause in Apache Tajo?

The HAVING clause enables you to specify conditions that filter which group results appear in the final results. The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on the groups created by the GROUP BY clause.

Syntax

Query

Result

The above query will generate the following result.

The query groups the records by age and returns the age when the condition result sum(mark) > 200.

What is Order By Clause in Apache Tajo?

The ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. The Tajo database sorts query results in ascending order by default.

Syntax

Query

Result

The above query will generate the following result.

Order by Clause

The query returns the names of those students in descending order whose marks are greater than 60.

How to create Index Statement in Apache Tajo?

The CREATE INDEX statement is used to create indexes in tables. Index is used for fast retrieval of data. Current version supports index for only plain TEXT formats stored on HDFS.

Syntax

Query

Result

The above query will generate the following result.

To view assigned index for the column, type the following query.

Here, TWO_LEVEL_BIN_TREE method is used by default in Tajo.

How to Drop Table Statement in Apache Tajo?

The Drop Table Statement is used to drop a table from the database.

Syntax

Query

To check if the table has been dropped from the table, type the following query.

Result

The above query will generate the following result.

Thee query can be checked using “\d” command to list out the available Tajo tables.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Apache Tajo Topics