Apache Tajo - Instruções SQL

No capítulo anterior, você entendeu como criar tabelas no Tajo. Este capítulo explica sobre a instrução SQL no Tajo.

Criar declaração de tabela

Antes de começar a criar uma tabela, crie um arquivo de texto “students.csv” no caminho do diretório de instalação do Tajo da seguinte maneira -

students.csv

Eu iria	Nome	Endereço	Era	Marcas
1	Adão	23 New Street	21	90
2	Amit	12 Old Street	13	95
3	Prumo	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	Maria	5 West Street	12	75
10	Peter	16 Park Avenue	12	95

Após a criação do arquivo, vá para o terminal e inicie o servidor Tajo e o shell, um por um.

Criar banco de dados

Crie um novo banco de dados usando o seguinte comando -

Inquerir

default> create database sampledb; 
OK

Conecte-se ao banco de dados “sampledb” que agora foi criado.

default> \c sampledb 
You are now connected to database "sampledb" as user “user1”.

Em seguida, crie uma tabela em “sampledb” da seguinte forma -

Inquerir

sampledb>  create external table mytable(id int,name text,address text,age int,mark int) 
   using text with('text.delimiter' = ',') location ‘file:/Users/workspace/Tajo/students.csv’;

Resultado

A consulta acima irá gerar o seguinte resultado.

OK

Aqui, a tabela externa é criada. Agora, você só precisa inserir a localização do arquivo. Se você tiver que atribuir a tabela de hdfs, use hdfs em vez de arquivo.

A seguir, o “students.csv”arquivo contém valores separados por vírgula. otext.delimiter campo é atribuído com ','.

Agora você criou “mytable” com sucesso em “sampledb”.

Mostrar mesa

Para mostrar tabelas no Tajo, use a seguinte consulta.

Inquerir

sampledb> \d  
mytable  
sampledb> \d mytable

Resultado

A consulta acima irá gerar o seguinte resultado.

table name: sampledb.mytable 
table uri: file:/Users/workspace/Tajo/students.csv 
store type: TEXT 
number of rows: unknown 
volume: 261 B
Options: 
'timezone' = 'Asia/Kolkata' 
'text.null' = '\\N' 
'text.delimiter' = ','  
schema: 
id INT4 
name TEXT 
address TEXT 
age INT4 
mark INT4

Tabela de lista

Para buscar todos os registros da tabela, digite a seguinte consulta -

Inquerir

sampledb> select * from mytable;

Resultado

A consulta acima irá gerar o seguinte resultado.

Inserir declaração de tabela

Tajo usa a seguinte sintaxe para inserir registros na tabela.

Sintaxe

create table table1 (col1 int8, col2 text, col3 text);   
--schema should be same for target table schema  
Insert overwrite into table1 select * from table2;  
                     (or)  
Insert overwrite into LOCATION '/dir/subdir' select * from table;

A declaração de inserção de Tajo é semelhante ao INSERT INTO SELECT declaração de SQL.

Inquerir

Vamos criar uma tabela para sobrescrever os dados da tabela de uma tabela existente.

sampledb> create table test(sno int,name text,addr text,age int,mark int);  
OK 
sampledb> \d

Resultado

A consulta acima irá gerar o seguinte resultado.

mytable 
test

Inserir registros

Para inserir registros na tabela “teste”, digite a seguinte consulta.

Inquerir

sampledb> insert overwrite into test select * from mytable;

Resultado

A consulta acima irá gerar o seguinte resultado.

Progress: 100%, response time: 0.518 sec

Aqui, os registros “mytable” substituem a tabela “test”. Se você não quiser criar a tabela “test”, atribua imediatamente o local do caminho físico, conforme mencionado em uma opção alternativa para inserir consulta.

Obter registros

Use a seguinte consulta para listar todos os registros na tabela de “teste” -

Inquerir

sampledb> select * from test;

Resultado

A consulta acima irá gerar o seguinte resultado.

Esta instrução é usada para adicionar, remover ou modificar colunas de uma tabela existente.

Para renomear a tabela, use a seguinte sintaxe -

Alter table table1 RENAME TO table2;

Inquerir

sampledb> alter table test rename to students;

Resultado

A consulta acima irá gerar o seguinte resultado.

OK

Para verificar o nome da tabela alterado, use a seguinte consulta.

sampledb> \d  
mytable 
students

Agora a tabela “teste” é alterada para tabela “alunos”.

Adicionar coluna

Para inserir uma nova coluna na tabela “alunos”, digite a seguinte sintaxe -

Alter table <table_name> ADD COLUMN <column_name> <data_type>

Inquerir

sampledb> alter table students add column grade text;

Resultado

A consulta acima irá gerar o seguinte resultado.

OK

Definir propriedade

Esta propriedade é usada para alterar a propriedade da tabela.

Inquerir

sampledb> ALTER TABLE students SET PROPERTY 'compression.type' = 'RECORD',
   'compression.codec' = 'org.apache.hadoop.io.compress.Snappy Codec' ; 
OK

Aqui, o tipo de compressão e as propriedades do codec são atribuídos.

Para alterar a propriedade do delimitador de texto, use o seguinte -

Inquerir

ALTER TABLE students  SET PROPERTY ‘text.delimiter'=',';  
OK

Resultado

A consulta acima irá gerar o seguinte resultado.

sampledb> \d students  
table name: sampledb.students 
table uri: file:/tmp/tajo-user1/warehouse/sampledb/students 
store type: TEXT 
number of rows: 10 
volume: 228 B 
Options: 
'compression.type' = 'RECORD' 
'timezone' = 'Asia/Kolkata' 
'text.null' = '\\N' 
'compression.codec' = 'org.apache.hadoop.io.compress.SnappyCodec' 
'text.delimiter' = ','  
schema: 
id INT4 
name TEXT 
addr TEXT 
age INT4 
mark INT4 
grade TEXT

O resultado acima mostra que as propriedades da tabela são alteradas através da propriedade “SET”.

Selecionar declaração

A instrução SELECT é usada para selecionar dados de um banco de dados.

A sintaxe para a instrução Select é a seguinte -

SELECT [distinct [all]] * | <expression> [[AS] <alias>] [, ...] 
   [FROM <table reference> [[AS] <table alias name>] [, ...]] 
   [WHERE <condition>] 
   [GROUP BY <expression> [, ...]] 
   [HAVING <condition>] 
   [ORDER BY <expression> [ASC|DESC] [NULLS (FIRST|LAST)] [, …]]

Cláusula Where

A cláusula Where é usada para filtrar registros da tabela.

Inquerir

sampledb> select * from mytable where id > 5;

Resultado

A consulta acima irá gerar o seguinte resultado.

A consulta retorna os registros dos alunos cujo id é maior que 5.

Inquerir

sampledb> select * from mytable where name = ‘Peter’;

Resultado

A consulta acima irá gerar o seguinte resultado.

Progress: 100%, response time: 0.117 sec 
 
  id,  name,  address,   age  
------------------------------- 
10,  Peter,  16 park avenue , 12

O resultado filtra apenas os registros de Peter.

Cláusula Distinta

Uma coluna da tabela pode conter valores duplicados. A palavra-chave DISTINCT pode ser usada para retornar apenas valores distintos (diferentes).

Sintaxe

SELECT DISTINCT column1,column2 FROM table_name;

Inquerir

sampledb> select distinct age from mytable;

Resultado

A consulta acima irá gerar o seguinte resultado.

Progress: 100%, response time: 0.216 sec  
age 
------------------------------- 
13 
12

A consulta retorna a idade distinta dos alunos de mytable.

Grupo por cláusula

A cláusula GROUP BY é usada em colaboração com a instrução SELECT para organizar dados idênticos em grupos.

Sintaxe

SELECT column1, column2 FROM table_name WHERE [ conditions ] GROUP BY column1, column2;

Inquerir

select age,sum(mark) as sumofmarks from mytable group by age;

Resultado

A consulta acima irá gerar o seguinte resultado.

age,  sumofmarks 
------------------------------- 
13,  145 
12,  610

Aqui, a coluna “mytable” tem dois tipos de idades - 12 e 13. Agora a consulta agrupa os registros por idade e produz a soma das notas para as idades correspondentes dos alunos.

Ter Cláusula

A cláusula HAVING permite que você especifique as condições que filtram quais resultados do grupo aparecem nos resultados finais. A cláusula WHERE coloca condições nas colunas selecionadas, enquanto a cláusula HAVING coloca condições nos grupos criados pela cláusula GROUP BY.

Sintaxe

SELECT column1, column2 FROM table1 GROUP BY column HAVING [ conditions ]

Inquerir

sampledb> select age from mytable group by age  having  sum(mark) > 200;

Resultado

A consulta acima irá gerar o seguinte resultado.

age 
------------------------------- 
12

A consulta agrupa os registros por idade e retorna a idade quando a soma do resultado da condição (marca)> 200.

Ordem por cláusula

A cláusula ORDER BY é usada para classificar os dados em ordem crescente ou decrescente, com base em uma ou mais colunas. O banco de dados Tajo classifica os resultados da consulta em ordem crescente por padrão.

Sintaxe

SELECT column-list FROM table_name  
[WHERE condition]  
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

Inquerir

sampledb> select * from mytable where mark > 60 order by name desc;

Resultado

A consulta acima irá gerar o seguinte resultado.

A consulta retorna os nomes dos alunos em ordem decrescente cujas notas são maiores que 60.

Criar declaração de índice

A instrução CREATE INDEX é usada para criar índices em tabelas. O índice é usado para recuperação rápida de dados. A versão atual suporta o índice apenas para formatos de texto simples armazenados em HDFS.

Sintaxe

CREATE INDEX [ name ] ON table_name ( { column_name | ( expression ) }

Inquerir

create index student_index on mytable(id);

Resultado

A consulta acima irá gerar o seguinte resultado.

id 
———————————————

Para visualizar o índice atribuído à coluna, digite a seguinte consulta.

default> \d mytable  
table name: default.mytable 
table uri: file:/Users/deiva/workspace/Tajo/students.csv 
store type: TEXT 
number of rows: unknown  
volume: 307 B 
Options: 
   'timezone' = 'Asia/Kolkata' 
   'text.null' = '\\N' 
   'text.delimiter' = ','  
schema: 
id INT4 
name TEXT 
address TEXT 
age INT4 
mark INT4   
Indexes:  
"student_index" TWO_LEVEL_BIN_TREE (id ASC NULLS LAST )

Aqui, o método TWO_LEVEL_BIN_TREE é usado por padrão no Tajo.

Declaração de abandono de mesa

A instrução Eliminar Tabela é usada para eliminar uma tabela do banco de dados.

Sintaxe

drop table table name;

Inquerir

sampledb> drop table mytable;

Para verificar se a tabela foi eliminada da tabela, digite a seguinte consulta.

sampledb> \d mytable;

Resultado

A consulta acima irá gerar o seguinte resultado.

ERROR: relation 'mytable' does not exist

Você também pode verificar a consulta usando o comando “\ d” para listar as tabelas Tajo disponíveis.