一 数据库与表

    • Mysql:

    • DB2:
      等待补充

    • Inceptor:

    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数据库

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

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

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

    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. --target-dir /user/chu888chu888/data/titles -m 4

    6 SQL SERVER导入的问题

    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 |       |
    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. +----------+-------------+------+-----+---------+-------+
    125. 5 rows in set (0.00 sec)
    126. create external table publishers
    128.   pub_id       STRING,
    129.   pub_name     STRING,
    130.   city         STRING,
    131.   state        STRING,
    132.   country      STRING
    133. )row format delimited fields terminated by ',' location '/user/chu888chu888/data/publishers';
    135. mysql> desc roysched;
    136. +----------+------------+------+-----+---------+-------+
    137. | Field    | Type       | Null | Key | Default | Extra |
    138. +----------+------------+------+-----+---------+-------+
    139. | title_id | varchar(6) | NO   | MUL | NULL    |       |
    140. | lorange  | int(11)    | YES  |     | NULL    |       |
    141. | hirange  | int(11)    | YES  |     | NULL    |       |
    142. | royalty  | int(11)    | YES  |     | NULL    |       |
    143. +----------+------------+------+-----+---------+-------+
    144. 4 rows in set (0.00 sec)
    145. create external table roysched
    146. (
    147.   title_id    STRING,
    148.   lorange     STRING,
    149.   hirange     STRING,
    150.   royalty     STRING
    151. )row format delimited fields terminated by ',' location '/user/chu888chu888/data/roysched';
    154. mysql> desc sales;
    155. +----------+-------------+------+-----+---------+-------+
    156. | Field    | Type        | Null | Key | Default | Extra |
    157. +----------+-------------+------+-----+---------+-------+
    158. | stor_id  | varchar(4)  | NO   | PRI | NULL    |       |
    159. | ord_num  | varchar(20) | NO   | PRI | NULL    |       |
    160. | ord_date | date        | YES  |     | NULL    |       |
    161. | qty      | smallint(6) | NO   |     | NULL    |       |
    162. | payterms | varchar(12) | NO   |     | NULL    |       |
    163. | title_id | varchar(6)  | NO   | PRI | NULL    |       |
    164. +----------+-------------+------+-----+---------+-------+
    165. 6 rows in set (0.00 sec)
    166. create external table sales
    167. (
    168.   stor_id    STRING,
    169.   ord_num    STRING,
    170.   ord_date   STRING,
    171.   qty        STRING,
    172.   title_id   STRING
    173. )row format delimited fields terminated by ',' location '/user/chu888chu888/data/sales';
    176. mysql> desc stores;
    177. +--------------+-------------+------+-----+---------+-------+
    178. | Field        | Type        | Null | Key | Default | Extra |
    179. +--------------+-------------+------+-----+---------+-------+
    180. | stor_id      | varchar(4)  | NO   | PRI | NULL    |       |
    181. | stor_name    | varchar(40) | YES  |     | NULL    |       |
    182. | stor_address | varchar(40) | YES  |     | NULL    |       |
    183. | city         | varchar(20) | YES  |     | NULL    |       |
    184. | state        | varchar(2)  | YES  |     | NULL    |       |
    185. | zip          | varchar(5)  | YES  |     | NULL    |       |
    186. +--------------+-------------+------+-----+---------+-------+
    187. 6 rows in set (0.01 sec)
    189. create external table stores
    190. (
    191.   stor_id          STRING,
    192.   stor_name        STRING,
    193.   stor_address     STRING,
    194.   city             STRING,
    195.   zip              STRING
    196. )row format delimited fields terminated by ',' location '/user/chu888chu888/data/stores';
    198. mysql> desc titleauthor;
    199. +------------+---------------------+------+-----+---------+-------+
    200. | Field      | Type                | Null | Key | Default | Extra |
    201. +------------+---------------------+------+-----+---------+-------+
    202. | au_id      | varchar(11)         | NO   | PRI | NULL    |       |
    203. | title_id   | varchar(6)          | NO   | PRI | NULL    |       |
    204. | au_ord     | tinyint(3) unsigned | YES  |     | NULL    |       |
    205. | royaltyper | int(11)             | YES  |     | NULL    |       |
    206. +------------+---------------------+------+-----+---------+-------+
    207. 4 rows in set (0.00 sec)
    209. create external table titleauthor
    210. (
    211.   au_id STRING,
    212.   title_id STRING,
    213.   au_ord TinyInt,
    214.   royaltyper INT
    215. )row format delimited fields terminated by ',' location '/user/chu888chu888/data/titleauthor';
    218. mysql> desc titles;
    219. +-----------+---------------+------+-----+-----------+-------+
    220. | Field     | Type          | Null | Key | Default   | Extra |
    221. +-----------+---------------+------+-----+-----------+-------+
    222. | title_id  | varchar(6)    | NO   | PRI | NULL      |       |
    223. | title     | varchar(80)   | NO   | MUL | NULL      |       |
    224. | type      | varchar(12)   | NO   |     | UNDECIDED |       |
    225. | pub_id    | varchar(4)    | YES  |     | NULL      |       |
    226. | price     | decimal(19,4) | YES  |     | NULL      |       |
    227. | advance   | decimal(19,4) | YES  |     | NULL      |       |
    228. | royalty   | int(11)       | YES  |     | NULL      |       |
    229. | ytd_sales | int(11)       | YES  |     | NULL      |       |
    230. | notes     | varchar(200)  | YES  |     | NULL      |       |
    231. | pubdate   | datetime      | NO   |     | NULL      |       |
    232. +-----------+---------------+------+-----+-----------+-------+
    233. 10 rows in set (0.00 sec)
    235. create external table titles
    236. (
    237.   title_id STRING,
    238.   title    STRING,
    239.   type     STRING,
    240.   pub_id   STRING,
    241.   price    STRING,
    242.   advance  STRING,
    243.   royalty  STRING,
    244.   ytd_sales STRING,
    245.   notes     STRING,
    247. )row format delimited fields terminated by ',' location '/user/chu888chu888/data/titles';