Apache Tajo 简明教程

<property>
   <name>tajo.rootdir</name>
   <value>hdfs://hostname:port/tajo</value>
</property>

<property>
   <name>tajo.master.umbilical-rpc.address</name>
   <value>hostname:26001</value>
</property>

<property>
   <name>tajo.master.client-rpc.address</name>
   <value>hostname:26002</value>
</property>

<property>
   <name>tajo.catalog.client-rpc.address</name>
   <value>hostname:26005</value>
</property>

default> \?

default> \l

information_schema
default

default> \c

default> \df

default> \df sqrt

default> \q

bye!

default> \admin -cluster

default> \admin -showmasters

localhost

default> default> create database if not exists test;

OK

default> \c

You are now connected to database "default" as user “user1".
default>

default> \c test

You are now connected to database "test" as user “user1”.
test>

test> \c default
You are now connected to database "default" as user “user1".
default> drop database test;

OK

create table table1(col1 int,col2 text);

$ vi conf/storage-site.json {
   "spaces": {
      "${tablespace_name}": {
         "uri": “hdfs://localhost:9000/path/to/Tajo"
      }
   }
}

$ vi conf/storage-site.json {
   "spaces": {
      "${tablespace_name}": {
         "uri": “hbase:zk://quorum1:port,quorum2:port/"
      }
   }
}

$ vi conf/storage-site.json {
   "spaces": {
      "${tablespace_name}": {
         “uri”: “hdfs://localhost:9000/path/to/Tajo”
      }
   }
}

CREATE TABLE [IF NOT EXISTS] <table_name> [(column_list)] [TABLESPACE tablespace_name]
[using <storage_type> [with (<key> = <value>, ...)]] [AS <select_statement>]

default> create table table1(num1 int,num2 text,num3 float) tablespace space1;

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

default> create table customer(id int,name text,address text,age int) using text;

default> create external table sample (num1 int,num2 text,num3 float)
   using json location ‘json/array.json’;

default> create table sample (num1 int,num2 text,num3 float) using json;

CREATE TABLE parquet (num1 int,num2 text,num3 float) USING PARQUET;

CREATE TABLE Record(num1 int,num2 text,num3 float) USING RCFILE;

CREATE TABLE seq(num1 int,num2 text,num3 float) USING sequencefile;

CREATE TABLE table1 (id int, name string, score float, type string)
STORED AS sequencefile;

CREATE TABLE optimized(num1 int,num2 text,num3 float) USING ORC;

default> create database sampledb;
OK

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

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’;

OK

sampledb> \d
mytable
sampledb> \d mytable

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

sampledb> select * from mytable;

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;

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

mytable
test

sampledb> insert overwrite into test select * from mytable;

Progress: 100%, response time: 0.518 sec

sampledb> select * from test;

Alter table table1 RENAME TO table2;

sampledb> alter table test rename to students;

OK

sampledb> \d
mytable
students

sampledb> alter table students add column grade text;

OK

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

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

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

sampledb> select * from mytable where id > 5;

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

Progress: 100%, response time: 0.117 sec

  id,  name,  address,   age
-------------------------------
10,  Peter,  16 park avenue , 12

SELECT DISTINCT column1,column2 FROM table_name;

sampledb> select distinct age from mytable;

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

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

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

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

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

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

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

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

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

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

create index student_index on mytable(id);

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

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 )

drop table table name;

sampledb> drop table mytable;

sampledb> \d mytable;

ERROR: relation 'mytable' does not exist

IN::=
<expression to test> [NOT] IN (<subquery>)
| (<expression1>,...)

select id,name,address from mytable where id in(2,3,4);

id,  name,   address
-------------------------------
2,  Amit,  12 old street
3,  Bob,   10 cross street
4,  David, 15 express avenue

select id,name,address from mytable where id not in(2,3,4);

id,  name,  address
-------------------------------
1,  Adam,   23 new street
5,  Esha,   20 garden street
6,  Ganga,  25 north street
7,  Jack,   2 park street
8,  Leena,  24 south street
9,  Mary,   5 west street
10, Peter,  16 park avenue

explain select * from mytable;
explain
-------------------------------
   => target list: default.mytable.id (INT4), default.mytable.name (TEXT),
      default.mytable.address (TEXT), default.mytable.age (INT4), default.mytable.mark (INT4)

   => out schema: {
   (5) default.mytable.id (INT4), default.mytable.name (TEXT), default.mytable.address (TEXT),
      default.mytable.age (INT4), default.mytable.mark (INT4)
   }

   => in schema: {
	(5) default.mytable.id (INT4), default.mytable.name (TEXT), default.mytable.address (TEXT),
      default.mytable.age (INT4), default.mytable.mark (INT4)
   }

explain global select * from mytable;
explain
-------------------------------
-------------------------------------------------------------------------------
Execution Block Graph (TERMINAL - eb_0000000000000_0000_000002)
-------------------------------------------------------------------------------
|-eb_0000000000000_0000_000002
   |-eb_0000000000000_0000_000001
-------------------------------------------------------------------------------
Order of Execution
-------------------------------------------------------------------------------
1: eb_0000000000000_0000_000001
2: eb_0000000000000_0000_000002
-------------------------------------------------------------------------------
=======================================================
Block Id: eb_0000000000000_0000_000001 [ROOT]
=======================================================
SCAN(0) on default.mytable

   => target list: default.mytable.id (INT4), default.mytable.name (TEXT),
      default.mytable.address (TEXT), default.mytable.age (INT4), default.mytable.mark (INT4)

   => out schema: {
	(5) default.mytable.id (INT4), default.mytable.name (TEXT),default.mytable.address (TEXT),
      default.mytable.age (INT4), default.mytable.mark (INT4)
   }

   => in schema: {
	(5) default.mytable.id (INT4), default.mytable.name (TEXT), default.mytable.address (TEXT),
      default.mytable.age (INT4), default.mytable.mark (INT4)
   }
=======================================================
Block Id: eb_0000000000000_0000_000002 [TERMINAL]
=======================================================
(24 rows, 0.065 sec, 0 B selected)

{
   "spaces": {
      "postgre": {
         "uri": "jdbc:postgresql://hostname:port/database1"
         "configs": {
            "mapped_database": “sampledb”
            "connection_properties": {
               "user":“tajo", "password": "pwd"
            }
         }
      }
   }
}

hbase(main):001:0> create ‘students’,{NAME => ’info’},{NAME => ’content’}
put 'students', ‘row-01', 'content:name', 'Adam'
put 'students', ‘row-01', 'info:id', '001'
put 'students', ‘row-02', 'content:name', 'Amit'
put 'students', ‘row-02', 'info:id', '002'
put 'students', ‘row-03', 'content:name', 'Bob'
put 'students', ‘row-03', 'info:id', ‘003'

main):001:0> cat ../hbase/hbase-students.txt | bin/hbase shell

default> \d students;

table name: default.students
table path:
store type: HBASE
number of rows: unknown
volume: 0 B
Options:
   'columns' = ':key,info:id,content:name'
   'table' = 'students'

schema:
rowkey  TEXT
id  INT4
name TEXT

default> select * from students

rowkey,  id,  name
-------------------------------
row-01,  001,  Adam
row-02,  002,  Amit
row-03   003,  Bob

import java.sql.*;
import org.apache.tajo.jdbc.TajoDriver;

public class TajoJdbcSample {
   public static void main(String[] args) {
      Connection connection = null;
      Statement statement = null;
      try {
         Class.forName("org.apache.tajo.jdbc.TajoDriver");
         connection = DriverManager.getConnection(“jdbc:tajo://localhost/default");
         statement = connection.createStatement();
         String sql;
         sql = "select * from mytable”;
         // fetch records from mytable.
         ResultSet resultSet = statement.executeQuery(sql);
         while(resultSet.next()){
            int id  = resultSet.getInt("id");
            String name = resultSet.getString("name");
            System.out.print("ID: " + id + ";\nName: " + name + "\n");
         }
         resultSet.close();
         statement.close();
         connection.close();
      }catch(SQLException sqlException){
         sqlException.printStackTrace();
      }catch(Exception exception){
         exception.printStackTrace();
      }
   }
}

javac -cp /path/to/tajo-jdbc-0.11.3.jar:. TajoJdbcSample.java

java -cp /path/to/tajo-jdbc-0.11.3.jar:. TajoJdbcSample

ID: 1;
Name: Adam

ID: 2;
Name: Amit

ID: 3;
Name: Bob

ID: 4;
Name: David

ID: 5;
Name: Esha

ID: 6;
Name: Ganga

ID: 7;
Name: Jack

ID: 8;
Name: Leena

ID: 9;
Name: Mary

ID: 10;
Name: Peter