Saturday, December 30, 2017

Apache Kudu DML example (kudu 1.5.0-cdh5.13.1)

[donghua@cdh-vm ~]$ impala-shell -i cdh-vm.dbaglobe.com -k
Starting Impala Shell using Kerberos authentication
Using service name 'impala'
Connected to cdh-vm.dbaglobe.com:21000
Server version: impalad version 2.10.0-cdh5.13.1 RELEASE (build 1e4b23c4eb52dac95c5be6316f49685c41783c51)
***********************************************************************************
Welcome to the Impala shell.
(Impala Shell v2.10.0-cdh5.13.1 (1e4b23c) built on Thu Nov  9 08:29:47 PST 2017)

To see a summary of a query's progress that updates in real-time, run 'set
LIVE_PROGRESS=1;'.
**********************************************************************************

[cdh-vm.dbaglobe.com:21000] > create table employees(id int, name string) stored as kudu;
Query: create table employees(id int, name string) stored as kudu
ERROR: AnalysisException: A primary key is required for a Kudu table.

[cdh-vm.dbaglobe.com:21000] > create table employees(id int, name string, primary key (id)) stored as kudu;
Query: create table employees(id int, name string, primary key (id)) stored as kudu
WARNINGS: Unpartitioned Kudu tables are inefficient for large data sizes.

Fetched 0 row(s) in 0.41s
[cdh-vm.dbaglobe.com:21000] > drop table employees;
Query: drop table employees

[cdh-vm.dbaglobe.com:21000] > create table employees(id int, name string, primary key (id)) partition by hash partitions 3 stored as kudu;
Query: create table employees(id int, name string, primary key (id)) partition by hash partitions 3 stored as kudu
Fetched 0 row(s) in 0.15s

[cdh-vm.dbaglobe.com:21000] > insert into employees values (1,'donghua');
Query: insert into employees values (1,'donghua')
Query submitted at: 2017-12-30 07:22:56 (Coordinator:
http://cdh-vm.dbaglobe.com:25000)
Query progress can be monitored at: http://cdh-vm.dbaglobe.com:25000/query_plan?query_id=724c4c67c59d5eb6:9c74075700000000
Modified 1 row(s), 0 row error(s) in 4.28s

[cdh-vm.dbaglobe.com:21000] > select * from employees;
Query: select * from employees
Query submitted at: 2017-12-30 07:23:12 (Coordinator:
http://cdh-vm.dbaglobe.com:25000)
Query progress can be monitored at: http://cdh-vm.dbaglobe.com:25000/query_plan?query_id=bb462f142f62e12b:385e2ce900000000
+----+---------+
| id | name    |
+----+---------+
| 1  | donghua |
+----+---------+
Fetched 1 row(s) in 0.16s

[cdh-vm.dbaglobe.com:21000] > insert into employees values (2,'larry');
Query: insert into employees values (2,'larry')
Query submitted at: 2017-12-30 07:23:21 (Coordinator:
http://cdh-vm.dbaglobe.com:25000)
Query progress can be monitored at: http://cdh-vm.dbaglobe.com:25000/query_plan?query_id=1a4767310c5a9b99:2c2a26b400000000
Modified 1 row(s), 0 row error(s) in 0.11s

[cdh-vm.dbaglobe.com:21000] > select * from employees;
Query: select * from employees
Query submitted at: 2017-12-30 07:23:26 (Coordinator:
http://cdh-vm.dbaglobe.com:25000)
Query progress can be monitored at: http://cdh-vm.dbaglobe.com:25000/query_plan?query_id=7d4b866c73311bd9:5374e5ad00000000
+----+---------+
| id | name    |
+----+---------+
| 2  | larry   |
| 1  | donghua |
+----+---------+
Fetched 2 row(s) in 0.16s

[cdh-vm.dbaglobe.com:21000] > update employees set id=3 where id=1;
Query: update employees set id=3 where id=1
Query submitted at: 2017-12-30 07:23:44 (Coordinator:
http://cdh-vm.dbaglobe.com:25000)
ERROR: AnalysisException: Key column 'id' cannot be updated.

[cdh-vm.dbaglobe.com:21000] > update employees set name='tom' where id=2;
Query: update employees set name='tom' where id=2
Query submitted at: 2017-12-30 07:23:58 (Coordinator:
http://cdh-vm.dbaglobe.com:25000)
Query progress can be monitored at: http://cdh-vm.dbaglobe.com:25000/query_plan?query_id=644fe7f97c2c5221:bc7730eb00000000
Modified 1 row(s), 0 row error(s) in 0.18s

[cdh-vm.dbaglobe.com:21000] > delete from employees where id=1;
Query: delete from employees where id=1
Query submitted at: 2017-12-30 07:24:11 (Coordinator:
http://cdh-vm.dbaglobe.com:25000)
Query progress can be monitored at: http://cdh-vm.dbaglobe.com:25000/query_plan?query_id=3048cecbb9e1c886:7686422c00000000
Modified 1 row(s), 0 row error(s) in 0.13s

[cdh-vm.dbaglobe.com:21000] > select * from employees;
Query: select * from employees
Query submitted at: 2017-12-30 07:24:16 (Coordinator:
http://cdh-vm.dbaglobe.com:25000)
Query progress can be monitored at: http://cdh-vm.dbaglobe.com:25000/query_plan?query_id=7244597f3717fcd0:5c81509d00000000
+----+------+
| id | name |
+----+------+
| 2  | tom  |
+----+------+
Fetched 1 row(s) in 0.14s
[cdh-vm.dbaglobe.com:21000] > exit;
Goodbye donghua