大数据hadoop之二十九.Sqooq1综合案例

数据库基本操作命令

1 选择数据库命令

Mysql:

登录方式:
#直接本地登录 root:123456
#mysql -u root -p 
#远程登录 192.168.1.178 chu888chu888:skybar
#mysql -h 192.168.1.178 -u chu888chu888 -p


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Northwind          |
| Pubs               |
| ReportServer       |
| hive               |
| hive_hadoop        |
| hivetestdb         |
| mysql              |
| performance_schema |
+--------------------+
9 rows in set (0.10 sec)

mysql> use Pubs;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_Pubs |
+----------------+
| authors        |
| discounts      |
| employee       |
| jobs           |
| pub_info       |
| publishers     |
| roysched       |
| sales          |
| stores         |
| titleauthor    |
| titles         |
+----------------+
11 rows in set (0.00 sec)

mysql> show columns from jobs;
+----------+---------------------+------+-----+-----------------------------------------+----------------+
| Field    | Type                | Null | Key | Default                                 | Extra          |
+----------+---------------------+------+-----+-----------------------------------------+----------------+
| job_id   | smallint(6)         | NO   | PRI | NULL                                    | auto_increment |
| job_desc | varchar(50)         | NO   |     | New Position - title not formalized yet |                |
| min_lvl  | tinyint(3) unsigned | NO   |     | NULL                                    |                |
| max_lvl  | tinyint(3) unsigned | NO   |     | NULL                                    |                |
+----------+---------------------+------+-----+-----------------------------------------+----------------+
4 rows in set (0.00 sec)

mysql> show status;
+------------------------------------------+-------------+
| Variable_name                            | Value       |
+------------------------------------------+-------------+
| Aborted_clients                          | 0           |
| Aborted_connects                         | 0           |
| Binlog_cache_disk_use                    | 0           |
| Binlog_cache_use                         | 0           |
| Binlog_stmt_cache_disk_use               | 0           |
| Binlog_stmt_cache_use                    | 0           |
----------------------------------------------------------

Inceptor:

[root@dhc-1 ~]# beeline -u jdbc:hive2://192.168.1.70:10000/
scan complete in 2ms
Connecting to jdbc:hive2://192.168.1.70:10000/
2016-03-22 08:33:48,094 INFO jdbc.Utils: Supplied authorities: 192.168.1.70:10000
2016-03-22 08:33:48,094 INFO jdbc.Utils: Resolved authority: 192.168.1.70:10000
Connected to: Apache Hive (version 0.12.0-transwarp-tdh40)
Driver: Hive JDBC (version 0.12.0-transwarp-tdh40)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 0.12.0-transwarp-tdh40 by Apache Hive
0: jdbc:hive2://192.168.1.70:10000/> show databases;
+----------------+
| database_name  |
+----------------+
| default        |
+----------------+
1 row selected (2.282 seconds)
0: jdbc:hive2://192.168.1.70:10000/> 

1 row selected (2.282 seconds)
0: jdbc:hive2://192.168.1.70:10000/> use default;
No rows affected (0.068 seconds)
0: jdbc:hive2://192.168.1.70:10000/> show tables;
+-----------+
| tab_name  |
+-----------+
+-----------+
No rows selected (0.08 seconds)
0: jdbc:hive2://192.168.1.70:10000/>

实验准备数据一 Pubs数据库

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

1 载入Inceptor

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

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

在Inceptor metastore节点服务器上安装sqoop服务
1
yum install sqoop
由于Inceptor-SQL中metastore中已经安装了mysql，就不需要安装mysql了
将mysql-connector-java-5.1.38tar.gz驱动包先解压
1
tar -zxvf mysql-connector-java-5.1.38tar.gz
cd进刚刚解压后的目录，将里面的mysql-connector-java-5.1.38-bin.jar包copy到/usr/lib/sqoop/lib本地目录下
从mysql————>HDFS上（import，将mysql中的db1数据库里面的表导入到/user/datadir，这里的datadir目录一定不要事先创建，不然会报错，语句执行的时候会自动创建目录的！最后一行的－m表示map成4个文件）

sqoop import \
--username chu888chu888 \
--password skybar \
--connect jdbc:mysql://192.168.1.178:3306/Pubs \
--table titleauthor \
--target-dir /user/chu888chu888/data/titleauthor -m 4

sqoop import \
--username chu888chu888 \
--password skybar \
--connect jdbc:mysql://192.168.1.178:3306/Pubs \
--table authors \
--target-dir /user/chu888chu888/data/authors -m 4

sqoop import \
--username chu888chu888 \
--password skybar \
--connect jdbc:mysql://192.168.1.178:3306/Pubs \
--table authors \
--target-dir /user/chu888chu888/data/employee -m 4

sqoop import \
--username chu888chu888 \
--password skybar \
--connect jdbc:mysql://192.168.1.178:3306/Pubs \
--table discounts \
--target-dir /user/chu888chu888/data/discounts -m 4

sqoop import \
--username chu888chu888 \
--password skybar \
--connect jdbc:mysql://192.168.1.178:3306/Pubs \
--table jobs \
--target-dir /user/chu888chu888/data/jobs -m 4


sqoop import \
--username chu888chu888 \
--password skybar \
--connect jdbc:mysql://192.168.1.178:3306/Pubs \
--table pub_info \
--target-dir /user/chu888chu888/data/pub_info -m 4


sqoop import \
--username chu888chu888 \
--password skybar \
--connect jdbc:mysql://192.168.1.178:3306/Pubs \
--table publishers \
--target-dir /user/chu888chu888/data/publishers -m 4

有一个问题如果表没有主键的话,就会导入不了.
alter table roysched add roysched_id int unsigned not Null auto_increment primary key;

sqoop import \
--username chu888chu888 \
--password skybar \
--connect jdbc:mysql://192.168.1.178:3306/Pubs \
--table roysched \
--target-dir /user/chu888chu888/data/roysched -m 4

sqoop import \
--username chu888chu888 \
--password skybar \
--connect jdbc:mysql://192.168.1.178:3306/Pubs \
--table sales \
--target-dir /user/chu888chu888/data/sales -m 4

sqoop import \
--username chu888chu888 \
--password skybar \
--connect jdbc:mysql://192.168.1.178:3306/Pubs \
--table stores \
--target-dir /user/chu888chu888/data/stores -m 4

sqoop import \
--username chu888chu888 \
--password skybar \
--connect jdbc:mysql://192.168.1.178:3306/Pubs \
--table titles \
--target-dir /user/chu888chu888/data/titles -m 4

SQL SERVER导入的问题

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

3 在Inceptor中建立外表结构

mysql> desc authors;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| au_id    | varchar(11) | NO   | PRI | NULL    |       |
| au_lname | varchar(40) | NO   | MUL | NULL    |       |
| au_fname | varchar(20) | NO   |     | NULL    |       |
| phone    | varchar(12) | NO   |     | UNKNOWN |       |
| address  | varchar(40) | YES  |     | NULL    |       |
| city     | varchar(20) | YES  |     | NULL    |       |
| state    | varchar(2)  | YES  |     | NULL    |       |
| zip      | varchar(5)  | YES  |     | NULL    |       |
| contract | bit(1)      | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
9 rows in set (0.00 sec)
create external table authors
(
  au_id               STRING,
  au_lname            STRING,
  au_fname            STRING,
  phone               STRING,
  address             STRING,
  city                STRING,
  state               STRING,
  zip                 STRING,
  contract            STRING
)row format delimited fields terminated by ',' location '/user/chu888chu888/data/authors';


mysql> desc discounts;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| discounttype | varchar(40)  | NO   |     | NULL    |       |
| stor_id      | varchar(4)   | YES  |     | NULL    |       |
| lowqty       | smallint(6)  | YES  |     | NULL    |       |
| highqty      | smallint(6)  | YES  |     | NULL    |       |
| discount     | decimal(6,2) | NO   |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
create external table discounts
(
  discounttype       STRING,
  stor_id            STRING,
  lowqty             STRING,
  highqty            STRING,
  discount           STRING,
  discount_id        STRING
)row format delimited fields terminated by ',' location '/user/chu888chu888/data/discounts';


mysql> desc employee;
+-----------+---------------------+------+-----+---------+-------+
| Field     | Type                | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| emp_id    | varchar(9)          | NO   | PRI | NULL    |       |
| fname     | varchar(20)         | NO   |     | NULL    |       |
| minit     | varchar(1)          | YES  |     | NULL    |       |
| lname     | varchar(30)         | NO   | MUL | NULL    |       |
| job_id    | smallint(6)         | NO   |     | 1       |       |
| job_lvl   | tinyint(3) unsigned | YES  |     | 10      |       |
| pub_id    | varchar(4)          | NO   |     | 9952    |       |
| hire_date | date                | YES  |     | NULL    |       |
+-----------+---------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

create external table employee
(
  emp_id       STRING,
  fname        STRING,
  minit        STRING,
  lname        STRING,
  job_id       STRING,
  job_lvl      STRING,
  pub_id       STRING,
  hire_date    STRING
)row format delimited fields terminated by ',' location '/user/chu888chu888/data/employee';


mysql> desc jobs;
+----------+---------------------+------+-----+-----------------------------------------+----------------+
| Field    | Type                | Null | Key | Default                                 | Extra          |
+----------+---------------------+------+-----+-----------------------------------------+----------------+
| job_id   | smallint(6)         | NO   | PRI | NULL                                    | auto_increment |
| job_desc | varchar(50)         | NO   |     | New Position - title not formalized yet |                |
| min_lvl  | tinyint(3) unsigned | NO   |     | NULL                                    |                |
| max_lvl  | tinyint(3) unsigned | NO   |     | NULL                                    |                |
+----------+---------------------+------+-----+-----------------------------------------+----------------+
4 rows in set (0.00 sec)
create external table jobs
(
  job_id       STRING,
  job_desc     STRING,
  min_lvl      STRING,
  max_lvl      STRING
)row format delimited fields terminated by ',' location '/user/chu888chu888/data/jobs';


mysql> desc pub_info;
+---------+------------+------+-----+---------+-------+
| Field   | Type       | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| pub_id  | varchar(4) | NO   | PRI | NULL    |       |
| logo    | longblob   | YES  |     | NULL    |       |
| pr_info | longtext   | YES  |     | NULL    |       |
+---------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

create external table pub_info
(
  pub_id       STRING,
  logo         STRING,
  pr_info      STRING
)row format delimited fields terminated by ',' location '/user/chu888chu888/data/pub_info';

mysql> desc publishers;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| pub_id   | varchar(4)  | NO   | PRI | NULL    |       |
| pub_name | varchar(40) | YES  |     | NULL    |       |
| city     | varchar(20) | YES  |     | NULL    |       |
| state    | varchar(2)  | YES  |     | NULL    |       |
| country  | varchar(30) | YES  |     | USA     |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
create external table publishers
(
  pub_id       STRING,
  pub_name     STRING,
  city         STRING,
  state        STRING,
  country      STRING
)row format delimited fields terminated by ',' location '/user/chu888chu888/data/publishers';

mysql> desc roysched;
+----------+------------+------+-----+---------+-------+
| Field    | Type       | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+-------+
| title_id | varchar(6) | NO   | MUL | NULL    |       |
| lorange  | int(11)    | YES  |     | NULL    |       |
| hirange  | int(11)    | YES  |     | NULL    |       |
| royalty  | int(11)    | YES  |     | NULL    |       |
+----------+------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
create external table roysched
(
  title_id    STRING,
  lorange     STRING,
  hirange     STRING,
  royalty     STRING
)row format delimited fields terminated by ',' location '/user/chu888chu888/data/roysched';


mysql> desc sales;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| stor_id  | varchar(4)  | NO   | PRI | NULL    |       |
| ord_num  | varchar(20) | NO   | PRI | NULL    |       |
| ord_date | date        | YES  |     | NULL    |       |
| qty      | smallint(6) | NO   |     | NULL    |       |
| payterms | varchar(12) | NO   |     | NULL    |       |
| title_id | varchar(6)  | NO   | PRI | NULL    |       |
+----------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
create external table sales
(
  stor_id    STRING,
  ord_num    STRING,
  ord_date   STRING,
  qty        STRING,
  title_id   STRING
)row format delimited fields terminated by ',' location '/user/chu888chu888/data/sales';


mysql> desc stores;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| stor_id      | varchar(4)  | NO   | PRI | NULL    |       |
| stor_name    | varchar(40) | YES  |     | NULL    |       |
| stor_address | varchar(40) | YES  |     | NULL    |       |
| city         | varchar(20) | YES  |     | NULL    |       |
| state        | varchar(2)  | YES  |     | NULL    |       |
| zip          | varchar(5)  | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

create external table stores
(
  stor_id          STRING,
  stor_name        STRING,
  stor_address     STRING,
  city             STRING,
  zip              STRING
)row format delimited fields terminated by ',' location '/user/chu888chu888/data/stores';

mysql> desc titleauthor;
+------------+---------------------+------+-----+---------+-------+
| Field      | Type                | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+-------+
| au_id      | varchar(11)         | NO   | PRI | NULL    |       |
| title_id   | varchar(6)          | NO   | PRI | NULL    |       |
| au_ord     | tinyint(3) unsigned | YES  |     | NULL    |       |
| royaltyper | int(11)             | YES  |     | NULL    |       |
+------------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

create external table titleauthor
(
  au_id STRING,
  title_id STRING,
  au_ord TinyInt,
  royaltyper INT
)row format delimited fields terminated by ',' location '/user/chu888chu888/data/titleauthor';


mysql> desc titles;
+-----------+---------------+------+-----+-----------+-------+
| Field     | Type          | Null | Key | Default   | Extra |
+-----------+---------------+------+-----+-----------+-------+
| title_id  | varchar(6)    | NO   | PRI | NULL      |       |
| title     | varchar(80)   | NO   | MUL | NULL      |       |
| type      | varchar(12)   | NO   |     | UNDECIDED |       |
| pub_id    | varchar(4)    | YES  |     | NULL      |       |
| price     | decimal(19,4) | YES  |     | NULL      |       |
| advance   | decimal(19,4) | YES  |     | NULL      |       |
| royalty   | int(11)       | YES  |     | NULL      |       |
| ytd_sales | int(11)       | YES  |     | NULL      |       |
| notes     | varchar(200)  | YES  |     | NULL      |       |
| pubdate   | datetime      | NO   |     | NULL      |       |
+-----------+---------------+------+-----+-----------+-------+
10 rows in set (0.00 sec)

create external table titles
(
  title_id STRING,
  title    STRING,
  type     STRING,
  pub_id   STRING,
  price    STRING,
  advance  STRING,
  royalty  STRING,
  ytd_sales STRING,
  notes     STRING,
  pubdate   STRING
)row format delimited fields terminated by ',' location '/user/chu888chu888/data/titles';

大数据hadoop之 二十九.Sqooq1综合案例

数据库基本操作命令

1 选择数据库命令

实验准备数据一 Pubs数据库

1 载入Inceptor

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

3 在Inceptor中建立外表结构