Saturday, December 23, 2017

Work with beeline output formating and DDL generation

[donghua@cdh-vm ~]$ beeline -u jdbc:hive2://cdh-vm.dbaglobe.com:10000/default -n donghua --silent=true
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> !outputformat table
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> select * from employees;
+------------------------+-----------------------+----------------------+-------------------+--+
| employees.employee_id  | employees.first_name  | employees.last_name  | employees.salary  |
+------------------------+-----------------------+----------------------+-------------------+--+
| 1                      | Donghua               | Luo                  | 1000.0            |
| 2                      | Larry                 | Elison               | 8000.0            |
| 3                      | Tom                   | Kyte                 | 5000.1            |
| 4                      | Tiger                 | Hood                 | 3443.4            |
+------------------------+-----------------------+----------------------+-------------------+--+
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> !outputformat vertical
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> select * from employees;
employees.employee_id  1
employees.first_name   Donghua
employees.last_name    Luo
employees.salary       1000.0

employees.employee_id  2
employees.first_name   Larry
employees.last_name    Elison
employees.salary       8000.0

employees.employee_id  3
employees.first_name   Tom
employees.last_name    Kyte
employees.salary       5000.1

employees.employee_id  4
employees.first_name   Tiger
employees.last_name    Hood
employees.salary       3443.4

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> !outputformat table

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> ! set maxColumnWidth 120
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> show create table monthly_taxi_fleet;
+----------------------------------------------------------------------+--+
|                            createtab_stmt                            |
+----------------------------------------------------------------------+--+
| CREATE EXTERNAL TABLE `monthly_taxi_fleet`(                          |
|   `month` char(7),                                                   |
|   `company` varchar(50),                                             |
|   `fleet` smallint)                                                  |
| ROW FORMAT SERDE                                                     |
|   'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'               |
| WITH SERDEPROPERTIES (                                               |
|   'field.delim'=',',                                                 |
|   'serialization.format'=',')                                        |
| STORED AS INPUTFORMAT                                                |
|   'org.apache.hadoop.mapred.TextInputFormat'                         |
| OUTPUTFORMAT                                                         |
|   'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'       |
| LOCATION                                                             |
|   'hdfs://cdh-vm.dbaglobe.com:8020/user/donghua/monthly_taxi_fleet'  |
| TBLPROPERTIES (                                                      |
|   'COLUMN_STATS_ACCURATE'='false',                                   |
|   'numFiles'='1',                                                    |
|   'numRows'='-1',                                                    |
|   'rawDataSize'='-1',                                                |
|   'skip.header.line.count'='1',                                      |
|   'totalSize'='25802',                                               |
|   'transient_lastDdlTime'='1514024883')                              |
+----------------------------------------------------------------------+--+


0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> !outputformat csv2
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> show create table monthly_taxi_fleet;
createtab_stmt
CREATE EXTERNAL TABLE `monthly_taxi_fleet`(
  `month` char(7),
  `company` varchar(50),
  `fleet` smallint)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
  'field.delim'=',',
  'serialization.format'=',')
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://cdh-vm.dbaglobe.com:8020/user/donghua/monthly_taxi_fleet'
TBLPROPERTIES (
  'COLUMN_STATS_ACCURATE'='false',
  'numFiles'='1',
  'numRows'='-1',
  'rawDataSize'='-1',
  'skip.header.line.count'='1',
  'totalSize'='25802',
  'transient_lastDdlTime'='1514024883')
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> !record monthly_taxi_fleet.ddl
Saving all output to "/home/donghua/monthly_taxi_fleet.ddl". Enter "record" with no arguments to stop it.
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> show create table monthly_taxi_fleet;
createtab_stmt
CREATE EXTERNAL TABLE `monthly_taxi_fleet`(
  `month` char(7),
  `company` varchar(50),
  `fleet` smallint)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
  'field.delim'=',',
  'serialization.format'=',')
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://cdh-vm.dbaglobe.com:8020/user/donghua/monthly_taxi_fleet'
TBLPROPERTIES (
  'COLUMN_STATS_ACCURATE'='false',
  'numFiles'='1',
  'numRows'='-1',
  'rawDataSize'='-1',
  'skip.header.line.count'='1',
  'totalSize'='25802',
  'transient_lastDdlTime'='1514024883')
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> !record
Recording stopped.


0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> !sh cat /home/donghua/monthly_taxi_fleet.ddl
createtab_stmt
CREATE EXTERNAL TABLE `monthly_taxi_fleet`(
  `month` char(7),
  `company` varchar(50),
  `fleet` smallint)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
  'field.delim'=',',
  'serialization.format'=',')
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://cdh-vm.dbaglobe.com:8020/user/donghua/monthly_taxi_fleet'
TBLPROPERTIES (
  'COLUMN_STATS_ACCURATE'='false',
  'numFiles'='1',
  'numRows'='-1',
  'rawDataSize'='-1',
  'skip.header.line.count'='1',
  'totalSize'='25802',
  'transient_lastDdlTime'='1514024883')