Apache Tajo - Pernyataan SQL
Di bab sebelumnya, Anda telah memahami cara membuat tabel di Tajo. Bab ini menjelaskan tentang pernyataan SQL di Tajo.
Buat Pernyataan Tabel
Sebelum pindah ke membuat tabel, buat file teks "students.csv" di jalur direktori instalasi Tajo sebagai berikut -
students.csv
Indo | Nama | Alamat | Usia | Tanda |
---|---|---|---|---|
1 | Adam | 23 Jalan Baru | 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 | Mendongkrak | 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 |
Setelah file dibuat, pindah ke terminal dan mulai server Tajo dan shell satu per satu.
Buat Database
Buat database baru menggunakan perintah berikut -
Pertanyaan
default> create database sampledb;
OK
Hubungkan ke database "sampledb" yang sekarang dibuat.
default> \c sampledb
You are now connected to database "sampledb" as user “user1”.
Kemudian, buat tabel di "sampledb" seperti berikut -
Pertanyaan
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’;
Hasil
Kueri di atas akan menghasilkan hasil sebagai berikut.
OK
Di sini, tabel eksternal dibuat. Sekarang, Anda hanya perlu memasukkan lokasi file. Jika Anda harus menetapkan tabel dari hdfs, gunakan hdfs sebagai ganti file.
Selanjutnya, “students.csv”file berisi nilai yang dipisahkan koma. Itutext.delimiter bidang diberikan dengan ','.
Anda sekarang telah berhasil membuat "tabel saya" di "sampledb".
Tunjukkan Tabel
Untuk memperlihatkan tabel di Tajo, gunakan kueri berikut ini.
Pertanyaan
sampledb> \d
mytable
sampledb> \d mytable
Hasil
Kueri di atas akan menghasilkan hasil sebagai berikut.
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
Tabel daftar
Untuk mengambil semua record dalam tabel, ketikkan query berikut -
Pertanyaan
sampledb> select * from mytable;
Hasil
Kueri di atas akan menghasilkan hasil sebagai berikut.
Sisipkan Pernyataan Tabel
Tajo menggunakan sintaks berikut untuk menyisipkan rekaman dalam tabel.
Sintaksis
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;
Pernyataan insert Tajo mirip dengan INSERT INTO SELECT pernyataan SQL.
Pertanyaan
Mari buat tabel untuk menimpa data tabel dari tabel yang sudah ada.
sampledb> create table test(sno int,name text,addr text,age int,mark int);
OK
sampledb> \d
Hasil
Kueri di atas akan menghasilkan hasil sebagai berikut.
mytable
test
Sisipkan Rekaman
Untuk menyisipkan record dalam tabel "test", ketik query berikut.
Pertanyaan
sampledb> insert overwrite into test select * from mytable;
Hasil
Kueri di atas akan menghasilkan hasil sebagai berikut.
Progress: 100%, response time: 0.518 sec
Di sini, catatan "mytable" menimpa tabel "test". Jika Anda tidak ingin membuat tabel "test", maka langsung tetapkan lokasi jalur fisik seperti yang disebutkan dalam opsi alternatif untuk menyisipkan kueri.
Ambil catatan
Gunakan query berikut untuk mendaftar semua record di tabel "test" -
Pertanyaan
sampledb> select * from test;
Hasil
Kueri di atas akan menghasilkan hasil sebagai berikut.
Pernyataan ini digunakan untuk menambah, menghapus, atau mengubah kolom dari tabel yang sudah ada.
Untuk mengganti nama tabel gunakan sintaks berikut -
Alter table table1 RENAME TO table2;
Pertanyaan
sampledb> alter table test rename to students;
Hasil
Kueri di atas akan menghasilkan hasil sebagai berikut.
OK
Untuk memeriksa nama tabel yang diubah, gunakan kueri berikut ini.
sampledb> \d
mytable
students
Sekarang tabel “test” diubah menjadi tabel “siswa”.
Tambahkan Kolom
Untuk menyisipkan kolom baru di tabel "siswa", ketik sintaks berikut -
Alter table <table_name> ADD COLUMN <column_name> <data_type>
Pertanyaan
sampledb> alter table students add column grade text;
Hasil
Kueri di atas akan menghasilkan hasil sebagai berikut.
OK
Tetapkan Properti
Properti ini digunakan untuk mengubah properti tabel.
Pertanyaan
sampledb> ALTER TABLE students SET PROPERTY 'compression.type' = 'RECORD',
'compression.codec' = 'org.apache.hadoop.io.compress.Snappy Codec' ;
OK
Di sini, tipe kompresi dan properti codec ditetapkan.
Untuk mengubah properti pembatas teks, gunakan yang berikut ini -
Pertanyaan
ALTER TABLE students SET PROPERTY ‘text.delimiter'=',';
OK
Hasil
Kueri di atas akan menghasilkan hasil sebagai berikut.
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
Hasil di atas menunjukkan bahwa properti tabel diubah menggunakan properti "SET".
Pilih Pernyataan
Pernyataan SELECT digunakan untuk memilih data dari database.
Sintaks untuk pernyataan Select adalah sebagai berikut -
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)] [, …]]
Dimana Klausul
Klausa Di mana digunakan untuk memfilter rekaman dari tabel.
Pertanyaan
sampledb> select * from mytable where id > 5;
Hasil
Kueri di atas akan menghasilkan hasil sebagai berikut.
Kueri mengembalikan rekaman siswa yang idnya lebih besar dari 5.
Pertanyaan
sampledb> select * from mytable where name = ‘Peter’;
Hasil
Kueri di atas akan menghasilkan hasil sebagai berikut.
Progress: 100%, response time: 0.117 sec
id, name, address, age
-------------------------------
10, Peter, 16 park avenue , 12
Hasilnya hanya menyaring catatan Peter.
Klausul Berbeda
Kolom tabel mungkin berisi nilai duplikat. Kata kunci DISTINCT dapat digunakan untuk mengembalikan hanya nilai yang berbeda (berbeda).
Sintaksis
SELECT DISTINCT column1,column2 FROM table_name;
Pertanyaan
sampledb> select distinct age from mytable;
Hasil
Kueri di atas akan menghasilkan hasil sebagai berikut.
Progress: 100%, response time: 0.216 sec
age
-------------------------------
13
12
Kueri mengembalikan usia siswa yang berbeda dari mytable.
Kelompok Menurut Klausul
Klausa GROUP BY digunakan berkolaborasi dengan pernyataan SELECT untuk menyusun data identik ke dalam grup.
Sintaksis
SELECT column1, column2 FROM table_name WHERE [ conditions ] GROUP BY column1, column2;
Pertanyaan
select age,sum(mark) as sumofmarks from mytable group by age;
Hasil
Kueri di atas akan menghasilkan hasil sebagai berikut.
age, sumofmarks
-------------------------------
13, 145
12, 610
Di sini, kolom "tabel saya" memiliki dua jenis usia - 12 dan 13. Sekarang kueri mengelompokkan catatan menurut usia dan menghasilkan jumlah nilai untuk usia siswa yang sesuai.
Memiliki Klausul
Klausa HAVING memungkinkan Anda untuk menentukan kondisi yang memfilter hasil grup mana yang muncul di hasil akhir. Klausa WHERE menempatkan kondisi pada kolom yang dipilih, sedangkan klausa HAVING menempatkan kondisi pada grup yang dibuat oleh klausa GROUP BY.
Sintaksis
SELECT column1, column2 FROM table1 GROUP BY column HAVING [ conditions ]
Pertanyaan
sampledb> select age from mytable group by age having sum(mark) > 200;
Hasil
Kueri di atas akan menghasilkan hasil sebagai berikut.
age
-------------------------------
12
Kueri mengelompokkan rekaman berdasarkan usia dan mengembalikan usia saat jumlah hasil kondisi (tanda)> 200.
Urutkan Berdasarkan Klausul
Klausa ORDER BY digunakan untuk mengurutkan data dalam urutan naik atau turun, berdasarkan satu atau beberapa kolom. Database Tajo mengurutkan hasil kueri dalam urutan menaik secara default.
Sintaksis
SELECT column-list FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];
Pertanyaan
sampledb> select * from mytable where mark > 60 order by name desc;
Hasil
Kueri di atas akan menghasilkan hasil sebagai berikut.
Kueri mengembalikan nama siswa tersebut dalam urutan menurun yang nilainya lebih dari 60.
Buat Pernyataan Indeks
Pernyataan CREATE INDEX digunakan untuk membuat indeks dalam tabel. Indeks digunakan untuk pengambilan data dengan cepat. Versi saat ini mendukung indeks hanya untuk format TEXT biasa yang disimpan di HDFS.
Sintaksis
CREATE INDEX [ name ] ON table_name ( { column_name | ( expression ) }
Pertanyaan
create index student_index on mytable(id);
Hasil
Kueri di atas akan menghasilkan hasil sebagai berikut.
id
———————————————
Untuk melihat indeks yang ditetapkan untuk kolom tersebut, ketikkan kueri berikut ini.
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 )
Di sini, metode TWO_LEVEL_BIN_TREE digunakan secara default di Tajo.
Pernyataan Drop Table
Pernyataan Drop Table digunakan untuk menjatuhkan tabel dari database.
Sintaksis
drop table table name;
Pertanyaan
sampledb> drop table mytable;
Untuk memeriksa apakah tabel telah dihapus dari tabel, ketikkan kueri berikut ini.
sampledb> \d mytable;
Hasil
Kueri di atas akan menghasilkan hasil sebagai berikut.
ERROR: relation 'mytable' does not exist
Anda juga dapat memeriksa kueri menggunakan perintah "\ d" untuk membuat daftar tabel Tajo yang tersedia.