一 数据库与表

数据库基本操作命令

1 选择数据库命令

  • Mysql:
  1. 登录方式:
  2. #直接本地登录 root:123456
  3. #mysql -u root -p 
  4. #远程登录 192.168.1.178 chu888chu888:skybar
  5. #mysql -h 192.168.1.178 -u chu888chu888 -p
  8. mysql> show databases;
  9. +--------------------+
  10. | Database           |
  11. +--------------------+
  12. | information_schema |
  13. | Northwind          |
  14. | Pubs               |
  15. | ReportServer       |
  16. | hive               |
  17. | hive_hadoop        |
  18. | hivetestdb         |
  19. | mysql              |
  20. | performance_schema |
  21. +--------------------+
  22. 9 rows in set (0.10 sec)
  24. mysql> use Pubs;
  25. Reading table information for completion of table and column names
  26. You can turn off this feature to get a quicker startup with -A
  28. Database changed
  29. mysql> show tables;
  30. +----------------+
  31. | Tables_in_Pubs |
  32. +----------------+
  33. | authors        |
  34. | discounts      |
  35. | employee       |
  36. | jobs           |
  37. | pub_info       |
  38. | publishers     |
  39. | roysched       |
  40. | sales          |
  41. | stores         |
  42. | titleauthor    |
  43. | titles         |
  44. +----------------+
  45. 11 rows in set (0.00 sec)
  47. mysql> show columns from jobs;
  48. +----------+---------------------+------+-----+-----------------------------------------+----------------+
  49. | Field    | Type                | Null | Key | Default                                 | Extra          |
  50. +----------+---------------------+------+-----+-----------------------------------------+----------------+
  51. | job_id   | smallint(6)         | NO   | PRI | NULL                                    | auto_increment |
  52. | job_desc | varchar(50)         | NO   |     | New Position - title not formalized yet |                |
  53. | min_lvl  | tinyint(3) unsigned | NO   |     | NULL                                    |                |
  54. | max_lvl  | tinyint(3) unsigned | NO   |     | NULL                                    |                |
  55. +----------+---------------------+------+-----+-----------------------------------------+----------------+
  56. 4 rows in set (0.00 sec)
  58. mysql> show status;
  59. +------------------------------------------+-------------+
  60. | Variable_name                            | Value       |
  61. +------------------------------------------+-------------+
  62. | Aborted_clients                          | 0           |
  63. | Aborted_connects                         | 0           |
  64. | Binlog_cache_disk_use                    | 0           |
  65. | Binlog_cache_use                         | 0           |
  66. | Binlog_stmt_cache_disk_use               | 0           |
  67. | Binlog_stmt_cache_use                    | 0           |
  68. ----------------------------------------------------------

  • Oracle:
    等待补充

  • DB2:
    等待补充

  • Inceptor:

  1. [root@dhc-1 ~]# beeline -u jdbc:hive2://192.168.1.70:10000/
  2. scan complete in 2ms
  3. Connecting to jdbc:hive2://192.168.1.70:10000/
  4. 2016-03-22 08:33:48,094 INFO jdbc.Utils: Supplied authorities: 192.168.1.70:10000
  5. 2016-03-22 08:33:48,094 INFO jdbc.Utils: Resolved authority: 192.168.1.70:10000
  6. Connected to: Apache Hive (version 0.12.0-transwarp-tdh40)
  7. Driver: Hive JDBC (version 0.12.0-transwarp-tdh40)
  8. Transaction isolation: TRANSACTION_REPEATABLE_READ
  9. Beeline version 0.12.0-transwarp-tdh40 by Apache Hive
  10. 0: jdbc:hive2://192.168.1.70:10000/> show databases;
  11. +----------------+
  12. | database_name  |
  13. +----------------+
  14. | default        |
  15. +----------------+
  16. 1 row selected (2.282 seconds)
  17. 0: jdbc:hive2://192.168.1.70:10000/> 
  19. 1 row selected (2.282 seconds)
  20. 0: jdbc:hive2://192.168.1.70:10000/> use default;
  21. No rows affected (0.068 seconds)
  22. 0: jdbc:hive2://192.168.1.70:10000/> show tables;
  23. +-----------+
  24. | tab_name  |
  25. +-----------+
  26. +-----------+
  27. No rows selected (0.08 seconds)
  28. 0: jdbc:hive2://192.168.1.70:10000/>

实验准备数据一 Pubs数据库

为了能在Inceptor中实现兼容性测试,我们必须去移植一下我们样例数据库中的数据(来之微软的Pubs数据库)

1 载入Inceptor

  1. --登录Inceptor server节点
  2. beeline -u jdbc:hive2://192.168.1.70:10000/

2 使用Sqoop将MYSQL数据库导入HDFS

  1. 在Inceptor metastore节点服务器上安装sqoop服务
    1. yum install sqoop

  2. 由于Inceptor-SQL中metastore中已经安装了mysql，就不需要安装mysql了

  3. 将mysql-connector-java-5.1.38tar.gz驱动包先解压

    1. tar -zxvf mysql-connector-java-5.1.38tar.gz

  4. cd进刚刚解压后的目录，将里面的mysql-connector-java-5.1.38-bin.jar包copy到/usr/lib/sqoop/lib本地目录下

  5. 从mysql————>HDFS上（import，将mysql中的db1数据库里面的表导入到/user/datadir，这里的datadir目录一定不要事先创建，不然会报错，语句执行的时候会自动创建目录的！最后一行的－m表示map成4个文件）

  1. sqoop import \
  2. --username chu888chu888 \
  3. --password skybar \
  4. --connect jdbc:mysql://192.168.1.178:3306/Pubs \
  5. --table titleauthor \
  6. --target-dir /user/chu888chu888/data/titleauthor -m 4
  8. sqoop import \
  9. --username chu888chu888 \
  10. --password skybar \
  11. --connect jdbc:mysql://192.168.1.178:3306/Pubs \
  12. --table authors \
  13. --target-dir /user/chu888chu888/data/authors -m 4
  15. sqoop import \
  16. --username chu888chu888 \
  17. --password skybar \
  18. --connect jdbc:mysql://192.168.1.178:3306/Pubs \
  19. --table authors \
  20. --target-dir /user/chu888chu888/data/employee -m 4
  22. sqoop import \
  23. --username chu888chu888 \
  24. --password skybar \
  25. --connect jdbc:mysql://192.168.1.178:3306/Pubs \
  26. --table discounts \
  27. --target-dir /user/chu888chu888/data/discounts -m 4
  29. sqoop import \
  30. --username chu888chu888 \
  31. --password skybar \
  32. --connect jdbc:mysql://192.168.1.178:3306/Pubs \
  33. --table jobs \
  34. --target-dir /user/chu888chu888/data/jobs -m 4
  37. sqoop import \
  38. --username chu888chu888 \
  39. --password skybar \
  40. --connect jdbc:mysql://192.168.1.178:3306/Pubs \
  41. --table pub_info \
  42. --target-dir /user/chu888chu888/data/pub_info -m 4
  45. sqoop import \
  46. --username chu888chu888 \
  47. --password skybar \
  48. --connect jdbc:mysql://192.168.1.178:3306/Pubs \
  49. --table publishers \
  50. --target-dir /user/chu888chu888/data/publishers -m 4
  53. 有一个问题如果表没有主键的话,就会导入不了.
  54. alter table roysched add roysched_id int unsigned not Null auto_increment primary key;
  56. sqoop import \
  57. --username chu888chu888 \
  58. --password skybar \
  59. --connect jdbc:mysql://192.168.1.178:3306/Pubs \
  60. --table roysched \
  61. --target-dir /user/chu888chu888/data/roysched -m 4
  63. sqoop import \
  64. --username chu888chu888 \
  65. --password skybar \
  66. --connect jdbc:mysql://192.168.1.178:3306/Pubs \
  67. --table sales \
  68. --target-dir /user/chu888chu888/data/sales -m 4
  70. sqoop import \
  71. --username chu888chu888 \
  72. --password skybar \
  73. --connect jdbc:mysql://192.168.1.178:3306/Pubs \
  74. --table stores \
  75. --target-dir /user/chu888chu888/data/stores -m 4
  77. sqoop import \
  78. --username chu888chu888 \
  79. --password skybar \
  80. --connect jdbc:mysql://192.168.1.178:3306/Pubs \
  81. --table titles \
  82. --target-dir /user/chu888chu888/data/titles -m 4

6 SQL SERVER导入的问题

  1. sqoop import \
  2. --table address \
  3. --connect "jdbc:sqlserver://192.168.1.139:1433;database=AdventureWorks" \
  4. --username=sa \
  5. --password=123456 \
  6. --hive-drop-import-delims \
  7. --null-string '\\N' \
  8. --null-non-string '\\N' \
  9. --fields-terminated-by '\001' \
  10. --target-dir /user/test/address1 -m 1

3 在Inceptor中建立外表结构

  1. mysql> desc authors;
  2. +----------+-------------+------+-----+---------+-------+
  3. | Field    | Type        | Null | Key | Default | Extra |
  4. +----------+-------------+------+-----+---------+-------+
  5. | au_id    | varchar(11) | NO   | PRI | NULL    |       |
  6. | au_lname | varchar(40) | NO   | MUL | NULL    |       |
  7. | au_fname | varchar(20) | NO   |     | NULL    |       |
  8. | phone    | varchar(12) | NO   |     | UNKNOWN |       |
  9. | address  | varchar(40) | YES  |     | NULL    |       |
  10. | city     | varchar(20) | YES  |     | NULL    |       |
  11. | state    | varchar(2)  | YES  |     | NULL    |       |
  12. | zip      | varchar(5)  | YES  |     | NULL    |       |
  13. | contract | bit(1)      | NO   |     | NULL    |       |
  14. +----------+-------------+------+-----+---------+-------+
  15. 9 rows in set (0.00 sec)
  16. create external table authors
  17. (
  18.   au_id               STRING,
  19.   au_lname            STRING,
  20.   au_fname            STRING,
  21.   phone               STRING,
  22.   address             STRING,
  23.   city                STRING,
  24.   state               STRING,
  25.   zip                 STRING,
  26.   contract            STRING
  27. )row format delimited fields terminated by ',' location '/user/chu888chu888/data/authors';
  30. mysql> desc discounts;
  31. +--------------+--------------+------+-----+---------+-------+
  32. | Field        | Type         | Null | Key | Default | Extra |
  33. +--------------+--------------+------+-----+---------+-------+
  34. | discounttype | varchar(40)  | NO   |     | NULL    |       |
  35. | stor_id      | varchar(4)   | YES  |     | NULL    |       |
  36. | lowqty       | smallint(6)  | YES  |     | NULL    |       |
  37. | highqty      | smallint(6)  | YES  |     | NULL    |       |
  38. | discount     | decimal(6,2) | NO   |     | NULL    |       |
  39. +--------------+--------------+------+-----+---------+-------+
  40. 5 rows in set (0.00 sec)
  41. create external table discounts
  42. (
  43.   discounttype       STRING,
  44.   stor_id            STRING,
  45.   lowqty             STRING,
  46.   highqty            STRING,
  47.   discount           STRING,
  48.   discount_id        STRING
  49. )row format delimited fields terminated by ',' location '/user/chu888chu888/data/discounts';
  52. mysql> desc employee;
  53. +-----------+---------------------+------+-----+---------+-------+
  54. | Field     | Type                | Null | Key | Default | Extra |
  55. +-----------+---------------------+------+-----+---------+-------+
  56. | emp_id    | varchar(9)          | NO   | PRI | NULL    |       |
  57. | fname     | varchar(20)         | NO   |     | NULL    |       |
  58. | minit     | varchar(1)          | YES  |     | NULL    |       |
  59. | lname     | varchar(30)         | NO   | MUL | NULL    |       |
  60. | job_id    | smallint(6)         | NO   |     | 1       |       |
  61. | job_lvl   | tinyint(3) unsigned | YES  |     | 10      |       |
  62. | pub_id    | varchar(4)          | NO   |     | 9952    |       |
  63. | hire_date | date                | YES  |     | NULL    |       |
  64. +-----------+---------------------+------+-----+---------+-------+
  65. 8 rows in set (0.00 sec)
  67. create external table employee
  68. (
  69.   emp_id       STRING,
  70.   fname        STRING,
  71.   minit        STRING,
  72.   lname        STRING,
  73.   job_id       STRING,
  74.   job_lvl      STRING,
  75.   pub_id       STRING,
  76.   hire_date    STRING
  77. )row format delimited fields terminated by ',' location '/user/chu888chu888/data/employee';
  80. mysql> desc jobs;
  81. +----------+---------------------+------+-----+-----------------------------------------+----------------+
  82. | Field    | Type                | Null | Key | Default                                 | Extra          |
  83. +----------+---------------------+------+-----+-----------------------------------------+----------------+
  84. | job_id   | smallint(6)         | NO   | PRI | NULL                                    | auto_increment |
  85. | job_desc | varchar(50)         | NO   |     | New Position - title not formalized yet |                |
  86. | min_lvl  | tinyint(3) unsigned | NO   |     | NULL                                    |                |
  87. | max_lvl  | tinyint(3) unsigned | NO   |     | NULL                                    |                |
  88. +----------+---------------------+------+-----+-----------------------------------------+----------------+
  89. 4 rows in set (0.00 sec)
  90. create external table jobs
  91. (
  92.   job_id       STRING,
  93.   job_desc     STRING,
  94.   min_lvl      STRING,
  95.   max_lvl      STRING
  96. )row format delimited fields terminated by ',' location '/user/chu888chu888/data/jobs';
  99. mysql> desc pub_info;
  100. +---------+------------+------+-----+---------+-------+
  101. | Field   | Type       | Null | Key | Default | Extra |
  102. +---------+------------+------+-----+---------+-------+
  103. | pub_id  | varchar(4) | NO   | PRI | NULL    |       |
  104. | logo    | longblob   | YES  |     | NULL    |       |
  105. | pr_info | longtext   | YES  |     | NULL    |       |
  106. +---------+------------+------+-----+---------+-------+
  107. 3 rows in set (0.00 sec)
  109. create external table pub_info
  110. (
  111.   pub_id       STRING,
  112.   logo         STRING,
  113.   pr_info      STRING
  114. )row format delimited fields terminated by ',' location '/user/chu888chu888/data/pub_info';
  116. mysql> desc publishers;
  117. +----------+-------------+------+-----+---------+-------+
  118. | Field    | Type        | Null | Key | Default | Extra |
  119. +----------+-------------+------+-----+---------+-------+
  120. | pub_id   | varchar(4)  | NO   | PRI | NULL    |       |
  121. | pub_name | varchar(40) | YES  |     | NULL    |       |
  122. | city     | varchar(20) | YES  |     | NULL    |       |
  123. | state    | varchar(2)  | YES  |     | NULL    |       |
  124. | country  | varchar(30) | YES  |     | USA     |       |
  125. +----------+-------------+------+-----+---------+-------+
  126. 5 rows in set (0.00 sec)
  127. create external table publishers
  128. (
  129.   pub_id       STRING,
  130.   pub_name     STRING,
  131.   city         STRING,
  132.   state        STRING,
  133.   country      STRING
  134. )row format delimited fields terminated by ',' location '/user/chu888chu888/data/publishers';
  136. mysql> desc roysched;
  137. +----------+------------+------+-----+---------+-------+
  138. | Field    | Type       | Null | Key | Default | Extra |
  139. +----------+------------+------+-----+---------+-------+
  140. | title_id | varchar(6) | NO   | MUL | NULL    |       |
  141. | lorange  | int(11)    | YES  |     | NULL    |       |
  142. | hirange  | int(11)    | YES  |     | NULL    |       |
  143. | royalty  | int(11)    | YES  |     | NULL    |       |
  144. +----------+------------+------+-----+---------+-------+
  145. 4 rows in set (0.00 sec)
  146. create external table roysched
  147. (
  148.   title_id    STRING,
  149.   lorange     STRING,
  150.   hirange     STRING,
  151.   royalty     STRING
  152. )row format delimited fields terminated by ',' location '/user/chu888chu888/data/roysched';
  155. mysql> desc sales;
  156. +----------+-------------+------+-----+---------+-------+
  157. | Field    | Type        | Null | Key | Default | Extra |
  158. +----------+-------------+------+-----+---------+-------+
  159. | stor_id  | varchar(4)  | NO   | PRI | NULL    |       |
  160. | ord_num  | varchar(20) | NO   | PRI | NULL    |       |
  161. | ord_date | date        | YES  |     | NULL    |       |
  162. | qty      | smallint(6) | NO   |     | NULL    |       |
  163. | payterms | varchar(12) | NO   |     | NULL    |       |
  164. | title_id | varchar(6)  | NO   | PRI | NULL    |       |
  165. +----------+-------------+------+-----+---------+-------+
  166. 6 rows in set (0.00 sec)
  167. create external table sales
  168. (
  169.   stor_id    STRING,
  170.   ord_num    STRING,
  171.   ord_date   STRING,
  172.   qty        STRING,
  173.   title_id   STRING
  174. )row format delimited fields terminated by ',' location '/user/chu888chu888/data/sales';
  177. mysql> desc stores;
  178. +--------------+-------------+------+-----+---------+-------+
  179. | Field        | Type        | Null | Key | Default | Extra |
  180. +--------------+-------------+------+-----+---------+-------+
  181. | stor_id      | varchar(4)  | NO   | PRI | NULL    |       |
  182. | stor_name    | varchar(40) | YES  |     | NULL    |       |
  183. | stor_address | varchar(40) | YES  |     | NULL    |       |
  184. | city         | varchar(20) | YES  |     | NULL    |       |
  185. | state        | varchar(2)  | YES  |     | NULL    |       |
  186. | zip          | varchar(5)  | YES  |     | NULL    |       |
  187. +--------------+-------------+------+-----+---------+-------+
  188. 6 rows in set (0.01 sec)
  190. create external table stores
  191. (
  192.   stor_id          STRING,
  193.   stor_name        STRING,
  194.   stor_address     STRING,
  195.   city             STRING,
  196.   zip              STRING
  197. )row format delimited fields terminated by ',' location '/user/chu888chu888/data/stores';
  199. mysql> desc titleauthor;
  200. +------------+---------------------+------+-----+---------+-------+
  201. | Field      | Type                | Null | Key | Default | Extra |
  202. +------------+---------------------+------+-----+---------+-------+
  203. | au_id      | varchar(11)         | NO   | PRI | NULL    |       |
  204. | title_id   | varchar(6)          | NO   | PRI | NULL    |       |
  205. | au_ord     | tinyint(3) unsigned | YES  |     | NULL    |       |
  206. | royaltyper | int(11)             | YES  |     | NULL    |       |
  207. +------------+---------------------+------+-----+---------+-------+
  208. 4 rows in set (0.00 sec)
  210. create external table titleauthor
  211. (
  212.   au_id STRING,
  213.   title_id STRING,
  214.   au_ord TinyInt,
  215.   royaltyper INT
  216. )row format delimited fields terminated by ',' location '/user/chu888chu888/data/titleauthor';
  219. mysql> desc titles;
  220. +-----------+---------------+------+-----+-----------+-------+
  221. | Field     | Type          | Null | Key | Default   | Extra |
  222. +-----------+---------------+------+-----+-----------+-------+
  223. | title_id  | varchar(6)    | NO   | PRI | NULL      |       |
  224. | title     | varchar(80)   | NO   | MUL | NULL      |       |
  225. | type      | varchar(12)   | NO   |     | UNDECIDED |       |
  226. | pub_id    | varchar(4)    | YES  |     | NULL      |       |
  227. | price     | decimal(19,4) | YES  |     | NULL      |       |
  228. | advance   | decimal(19,4) | YES  |     | NULL      |       |
  229. | royalty   | int(11)       | YES  |     | NULL      |       |
  230. | ytd_sales | int(11)       | YES  |     | NULL      |       |
  231. | notes     | varchar(200)  | YES  |     | NULL      |       |
  232. | pubdate   | datetime      | NO   |     | NULL      |       |
  233. +-----------+---------------+------+-----+-----------+-------+
  234. 10 rows in set (0.00 sec)
  236. create external table titles
  237. (
  238.   title_id STRING,
  239.   title    STRING,
  240.   type     STRING,
  241.   pub_id   STRING,
  242.   price    STRING,
  243.   advance  STRING,
  244.   royalty  STRING,
  245.   ytd_sales STRING,
  246.   notes     STRING,
  247.   pubdate   STRING
  248. )row format delimited fields terminated by ',' location '/user/chu888chu888/data/titles';