Exercise 06 - Sqoop import and export

sqoop
#1

Reference Documentation - https://sqoop.apache.org/docs/1.4.6/SqoopUserGuide.html

Problem:

  • Create HDFS directory with name retail_data
  • Import all tables using avro file format, compression and number of mappers as 2
  • Please use this hint for avro format - -Dmapreduce.job.user.classpath.first=true
  • Import orders table to hive database of yours (text file) - Using sqoop import with --hive-import and delimiter ‘|’ - use table name orders_sqooped
  • Export the output of revenue per day per department query to mysql
  • Create table in mysql logging in as retail_dba
  • Database - retail_export
  • Username - retail_dba

Please provide the following

  • Output of hadoop fs -ls -R /user/YOUR_USER_NAME/retail_data
  • Output of describe formatted orders_sqooped
  • Output of select * from retail_export.YOUR_TABLE_NAME limit 10
1 Like

#2

sqoop import-all-tables -D mapreduce.job.user.classpath.first=true
–num-mappers 2
–connect “jdbc:mysql://nn01.itversity.com:3306/retail_db” --username=retail_dba --P
–as-avrodatafile
–compress --compression-codec org.apache.hadoop.io.compress.SnappyCodec
–warehouse-dir /user/infosnehasish/retail_data


sqoop import
–connect “jdbc:mysql://nn01.itversity.com:3306/retail_db”
–username=retail_dba --password=itversity
–table orders
–fields-terminated-by ‘|’
–lines-terminated-by ‘\n’
–hive-home /apps/hive/warehouse
–hive-import
–hive-table infosnehasish.orders_sqooped
–create-hive-table


#Output of hadoop fs -ls -R /user/YOUR_USER_NAME/retail_data

[infosnehasish@gw01 oozie_demo]$ hadoop fs -ls -R retail_data
drwxr-xr-x - infosnehasish hdfs 0 2016-12-15 04:20 retail_data/categories
-rw-r–r-- 3 infosnehasish hdfs 0 2016-12-15 04:20 retail_data/categories/_SUCCESS
-rw-r–r-- 3 infosnehasish hdfs 989 2016-12-15 04:20 retail_data/categories/part-m-00000.avro
-rw-r–r-- 3 infosnehasish hdfs 963 2016-12-15 04:20 retail_data/categories/part-m-00001.avro
drwxr-xr-x - infosnehasish hdfs 0 2016-12-15 04:21 retail_data/customers
-rw-r–r-- 3 infosnehasish hdfs 0 2016-12-15 04:21 retail_data/customers/_SUCCESS
-rw-r–r-- 3 infosnehasish hdfs 216186 2016-12-15 04:21 retail_data/customers/part-m-00000.avro
-rw-r–r-- 3 infosnehasish hdfs 218877 2016-12-15 04:21 retail_data/customers/part-m-00001.avro
drwxr-xr-x - infosnehasish hdfs 0 2016-12-15 04:21 retail_data/departments
-rw-r–r-- 3 infosnehasish hdfs 0 2016-12-15 04:21 retail_data/departments/_SUCCESS
-rw-r–r-- 3 infosnehasish hdfs 479 2016-12-15 04:21 retail_data/departments/part-m-00000.avro
-rw-r–r-- 3 infosnehasish hdfs 427 2016-12-15 04:21 retail_data/departments/part-m-00001.avro
drwxr-xr-x - infosnehasish hdfs 0 2016-12-15 04:21 retail_data/order_items
-rw-r–r-- 3 infosnehasish hdfs 0 2016-12-15 04:21 retail_data/order_items/_SUCCESS
-rw-r–r-- 3 infosnehasish hdfs 751458 2016-12-15 04:21 retail_data/order_items/part-m-00000.avro
-rw-r–r-- 3 infosnehasish hdfs 746088 2016-12-15 04:21 retail_data/order_items/part-m-00001.avro
drwxr-xr-x - infosnehasish hdfs 0 2016-12-15 04:22 retail_data/orders
-rw-r–r-- 3 infosnehasish hdfs 0 2016-12-15 04:22 retail_data/orders/_SUCCESS
-rw-r–r-- 3 infosnehasish hdfs 324326 2016-12-15 04:22 retail_data/orders/part-m-00000.avro
-rw-r–r-- 3 infosnehasish hdfs 329255 2016-12-15 04:22 retail_data/orders/part-m-00001.avro
drwxr-xr-x - infosnehasish hdfs 0 2016-12-15 04:22 retail_data/products
-rw-r–r-- 3 infosnehasish hdfs 0 2016-12-15 04:22 retail_data/products/_SUCCESS
-rw-r–r-- 3 infosnehasish hdfs 23126 2016-12-15 04:22 retail_data/products/part-m-00000.avro
-rw-r–r-- 3 infosnehasish hdfs 28371 2016-12-15 04:22 retail_data/products/part-m-00001.avro


#Output of describe formatted orders_sqooped

hive> describe formatted orders_sqooped;
OK

col_name data_type comment

order_id int
order_date string
order_customer_id int
order_status string

Detailed Table Information

Database: infosnehasish
Owner: infosnehasish
CreateTime: Thu Dec 15 04:25:24 EST 2016
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://nn01.itversity.com:8020/apps/hive/warehouse/infosnehasish.db/orders_sqooped
Table Type: MANAGED_TABLE
Table Parameters:
comment Imported by sqoop on 2016/12/15 04:25:21
numFiles 4
numRows 0
rawDataSize 0
totalSize 2999944
transient_lastDdlTime 1481793925

Storage Information

SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
field.delim |
line.delim \n
serialization.format |
Time taken: 0.448 seconds, Fetched: 36 row(s)


sqoop export --connect “jdbc:mysql://nn01.itversity.com:3306/retail_export”
–username retail_dba
–password itversity
–table daywise_dept_revenue
–input-fields-terminated-by ‘\001’
–export-dir /apps/hive/warehouse/infosnehasish.db/daywise_dept_revenue


#Output of select * from retail_export.YOUR_TABLE_NAME limit 10

[infosnehasish@gw01 ~]$ sqoop eval --connect “jdbc:mysql://nn01.itversity.com/retail_export” \

–username retail_dba --password itversity
–query "SELECT * FROM daywise_dept_revenue LIMIT 10"
Warning: /usr/hdp/2.5.0.0-1245/accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
16/12/15 05:44:31 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6.2.5.0.0-1245
16/12/15 05:44:31 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
16/12/15 05:44:31 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.


| order_date | department_name | revene_per_day |

| 2013-07-25 00:00:00.0 | Apparel | 3279.5701179504395 |
| 2013-07-25 00:00:00.0 | Fan Shop | 9798.69021987915 |
| 2013-07-25 00:00:00.0 | Fitness | 394.92999267578125 |
| 2013-07-25 00:00:00.0 | Footwear | 3899.6100540161133 |
| 2013-07-25 00:00:00.0 | Golf | 2029.720012664795 |
| 2013-07-25 00:00:00.0 | Outdoors | 627.799991607666 |
| 2013-07-26 00:00:00.0 | Apparel | 8828.750286102295 |
| 2013-07-26 00:00:00.0 | Fan Shop | 20847.68049621582 |
| 2013-07-26 00:00:00.0 | Fitness | 183.9800033569336 |
| 2013-07-26 00:00:00.0 | Footwear | 5129.419956207275 |

0 Likes

#3

[saswat232@gw01 ~]$ hadoop fs -ls -R /user/saswat232/retail_data;
drwxr-xr-x - saswat232 hdfs 0 2016-12-15 04:48 /user/saswat232/retail_data/categories
-rw-r–r-- 3 saswat232 hdfs 0 2016-12-15 04:48 /user/saswat232/retail_data/categories/_SUCCESS
-rw-r–r-- 3 saswat232 hdfs 856 2016-12-15 04:48 /user/saswat232/retail_data/categories/part-m-00000.avro
-rw-r–r-- 3 saswat232 hdfs 838 2016-12-15 04:48 /user/saswat232/retail_data/categories/part-m-00001.avro
drwxr-xr-x - saswat232 hdfs 0 2016-12-15 04:49 /user/saswat232/retail_data/customers
-rw-r–r-- 3 saswat232 hdfs 0 2016-12-15 04:49 /user/saswat232/retail_data/customers/_SUCCESS
-rw-r–r-- 3 saswat232 hdfs 154461 2016-12-15 04:49 /user/saswat232/retail_data/customers/part-m-00000.avro
-rw-r–r-- 3 saswat232 hdfs 156051 2016-12-15 04:49 /user/saswat232/retail_data/customers/part-m-00001.avro
drwxr-xr-x - saswat232 hdfs 0 2016-12-15 04:49 /user/saswat232/retail_data/departments
-rw-r–r-- 3 saswat232 hdfs 0 2016-12-15 04:49 /user/saswat232/retail_data/departments/_SUCCESS
-rw-r–r-- 3 saswat232 hdfs 474 2016-12-15 04:49 /user/saswat232/retail_data/departments/part-m-00000.avro
-rw-r–r-- 3 saswat232 hdfs 424 2016-12-15 04:49 /user/saswat232/retail_data/departments/part-m-00001.avro
drwxr-xr-x - saswat232 hdfs 0 2016-12-15 04:49 /user/saswat232/retail_data/order_items
-rw-r–r-- 3 saswat232 hdfs 0 2016-12-15 04:49 /user/saswat232/retail_data/order_items/_SUCCESS
-rw-r–r-- 3 saswat232 hdfs 404911 2016-12-15 04:49 /user/saswat232/retail_data/order_items/part-m-00000.avro
-rw-r–r-- 3 saswat232 hdfs 397137 2016-12-15 04:49 /user/saswat232/retail_data/order_items/part-m-00001.avro
drwxr-xr-x - saswat232 hdfs 0 2016-12-15 04:50 /user/saswat232/retail_data/orders
-rw-r–r-- 3 saswat232 hdfs 0 2016-12-15 04:50 /user/saswat232/retail_data/orders/_SUCCESS
-rw-r–r-- 3 saswat232 hdfs 200569 2016-12-15 04:50 /user/saswat232/retail_data/orders/part-m-00000.avro
-rw-r–r-- 3 saswat232 hdfs 204616 2016-12-15 04:50 /user/saswat232/retail_data/orders/part-m-00001.avro
drwxr-xr-x - saswat232 hdfs 0 2016-12-15 04:50 /user/saswat232/retail_data/products
-rw-r–r-- 3 saswat232 hdfs 0 2016-12-15 04:50 /user/saswat232/retail_data/products/_SUCCESS
-rw-r–r-- 3 saswat232 hdfs 15746 2016-12-15 04:50 /user/saswat232/retail_data/products/part-m-00000.avro
-rw-r–r-- 3 saswat232 hdfs 19248 2016-12-15 04:50 /user/saswat232/retail_data/products/part-m-00001.avro

================================================================================================================================
describe formatted sqoop_orders;
OK

col_name data_type comment

order_id int
order_date string
order_customer_id int
order_status string

Detailed Table Information

Database: saswat232
Owner: saswat232
CreateTime: Thu Dec 15 05:01:10 EST 2016
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://nn01.itversity.com:8020/apps/hive/warehouse/saswat232.db/sqoop_orders
Table Type: MANAGED_TABLE
Table Parameters:
comment Imported by sqoop on 2016/12/15 05:01:06
numFiles 4
numRows 0
rawDataSize 0
totalSize 2999944
transient_lastDdlTime 1481796071

Storage Information

SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
field.delim |
line.delim \n
serialization.format |
Time taken: 0.369 seconds, Fetched: 36 row(s)

================================================================================================================================

select * from saswat_revenue_per_day limit 10;
±--------------------±---------±------------+
| out_date | out_dept | out_revenue |
±--------------------±---------±------------+
| 2014-01-22 00:00:00 | Fan Shop | 17398.4 |
| 2014-01-22 00:00:00 | Fitness | 629.95 |
| 2014-01-22 00:00:00 | Footwear | 3099.69 |
| 2014-01-22 00:00:00 | Golf | 5909.71 |
| 2014-01-22 00:00:00 | Outdoors | 553.8 |
| 2014-01-23 00:00:00 | Apparel | 6429.22 |
| 2014-01-23 00:00:00 | Fan Shop | 14348.7 |
| 2014-01-23 00:00:00 | Fitness | 474.95 |
| 2014-01-23 00:00:00 | Footwear | 4224.65 |
| 2014-01-23 00:00:00 | Golf | 3464.74 |
±--------------------±---------±------------+
10 rows in set (0.00 sec)

0 Likes

#4

[sumanthsharma21@gw01 ~]$ hadoop fs -ls /user/sumanthsharma21/db_retail_avro
Found 6 items
drwxr-xr-x - sumanthsharma21 hdfs 0 2016-12-15 05:34 /user/sumanthsharma21/db_retail_avro/categories
drwxr-xr-x - sumanthsharma21 hdfs 0 2016-12-15 05:34 /user/sumanthsharma21/db_retail_avro/customers
drwxr-xr-x - sumanthsharma21 hdfs 0 2016-12-15 05:34 /user/sumanthsharma21/db_retail_avro/departments
drwxr-xr-x - sumanthsharma21 hdfs 0 2016-12-15 05:35 /user/sumanthsharma21/db_retail_avro/order_items
drwxr-xr-x - sumanthsharma21 hdfs 0 2016-12-15 05:35 /user/sumanthsharma21/db_retail_avro/orders
drwxr-xr-x - sumanthsharma21 hdfs 0 2016-12-15 05:35 /user/sumanthsharma21/db_retail_avro/products
[sumanthsharma21@gw01 ~]$ hadoop fs -ls /user/sumanthsharma21/db_retail_avro/categories
Found 3 items
-rw-r–r-- 3 sumanthsharma21 hdfs 0 2016-12-15 05:34 /user/sumanthsharma21/db_retail_avro/categories/_SUCCESS
-rw-r–r-- 3 sumanthsharma21 hdfs 1039 2016-12-15 05:34 /user/sumanthsharma21/db_retail_avro/categories/part-m-00000.avro
-rw-r–r-- 3 sumanthsharma21 hdfs 991 2016-12-15 05:34 /user/sumanthsharma21/db_retail_avro/categories/part-m-00001.avro
[sumanthsharma21@gw01 ~]$

describe formatted sumanthsharma21.orders_sqoop;

col_name data_type comment

col_name data_type comment

“” null null
order_id int ""
order_date string ""
order_customer_id int ““
order_status string “”
”” null null

Detailed Table Information null null

Database: sumanthsharma21 null
Owner: sumanthsharma21 null
CreateTime: Thu Dec 15 05:45:21 EST 2016 null
LastAccessTime: UNKNOWN null
Protect Mode: None null
Retention: 0 null
Location: hdfs://nn01.itversity.com:8020/apps/hive/warehouse/sumanthsharma21.db/orders_sqoop null
Table Type: MANAGED_TABLE null
Table Parameters: null null
"" comment Imported by sqoop on 2016/12/15 05:45:17
"" numFiles 2
"" numRows 0
"" rawDataSize 0
"" totalSize 2999944
"" transient_lastDdlTime 1481798722
"" null null

Storage Information null null

SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe null
InputFormat: org.apache.hadoop.mapred.TextInputFormat null
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat null
Compressed: No null
Num Buckets: -1 null
Bucket Columns: [] null
Sort Columns: [] null
Storage Desc Params: null null
"" field.delim |
"" line.delim \n
"" serialization.format |
SQL
TEZ

mysql> use retail_export;
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> select * from sumanthsharma21_fact_rev_per_day limit 10;
±--------------------±----------------±-------------------+
| order_date | department_name | revenue_per_day |
±--------------------±----------------±-------------------+
| 2013-10-22 00:00:00 | Fitness | 174.98999786376953 |
| 2013-10-22 00:00:00 | Footwear | 99.98999786376953 |
| 2013-10-23 00:00:00 | Apparel | 7319.060234069824 |
| 2013-10-23 00:00:00 | Fan Shop | 13048.490268707275 |
| 2013-10-23 00:00:00 | Fitness | 124.94999694824219 |
| 2013-10-23 00:00:00 | Footwear | 2399.97998046875 |
| 2013-10-24 00:00:00 | Apparel | 4089.470142364502 |
| 2013-10-24 00:00:00 | Fan Shop | 9549.050216674805 |
| 2013-10-24 00:00:00 | Fitness | 50 |
| 2013-10-25 00:00:00 | Apparel | 5719.2802085876465 |
±--------------------±----------------±-------------------+
10 rows in set (0.00 sec)

0 Likes

#5

[nagellarajashyam@gw01 ~]$ hdfs dfs -ls -R /user/nagellarajashyam/retail_data
drwxr-xr-x - nagellarajashyam hdfs 0 2016-12-15 04:11 /user/nagellarajashyam/retail_data/categories
-rw-r–r-- 3 nagellarajashyam hdfs 0 2016-12-15 04:11 /user/nagellarajashyam/retail_data/categories/_SUCCESS
-rw-r–r-- 3 nagellarajashyam hdfs 989 2016-12-15 04:11 /user/nagellarajashyam/retail_data/categories/part-m-00000.avro
-rw-r–r-- 3 nagellarajashyam hdfs 963 2016-12-15 04:11 /user/nagellarajashyam/retail_data/categories/part-m-00001.avro
drwxr-xr-x - nagellarajashyam hdfs 0 2016-12-15 04:12 /user/nagellarajashyam/retail_data/customers
-rw-r–r-- 3 nagellarajashyam hdfs 0 2016-12-15 04:12 /user/nagellarajashyam/retail_data/customers/_SUCCESS
-rw-r–r-- 3 nagellarajashyam hdfs 216186 2016-12-15 04:12 /user/nagellarajashyam/retail_data/customers/part-m-00000.avro
-rw-r–r-- 3 nagellarajashyam hdfs 218877 2016-12-15 04:12 /user/nagellarajashyam/retail_data/customers/part-m-00001.avro
drwxr-xr-x - nagellarajashyam hdfs 0 2016-12-15 04:12 /user/nagellarajashyam/retail_data/departments
-rw-r–r-- 3 nagellarajashyam hdfs 0 2016-12-15 04:12 /user/nagellarajashyam/retail_data/departments/_SUCCESS
-rw-r–r-- 3 nagellarajashyam hdfs 479 2016-12-15 04:12 /user/nagellarajashyam/retail_data/departments/part-m-00000.avro
-rw-r–r-- 3 nagellarajashyam hdfs 427 2016-12-15 04:12 /user/nagellarajashyam/retail_data/departments/part-m-00001.avro
drwxr-xr-x - nagellarajashyam hdfs 0 2016-12-15 04:12 /user/nagellarajashyam/retail_data/order_items
-rw-r–r-- 3 nagellarajashyam hdfs 0 2016-12-15 04:12 /user/nagellarajashyam/retail_data/order_items/_SUCCESS
-rw-r–r-- 3 nagellarajashyam hdfs 751458 2016-12-15 04:12 /user/nagellarajashyam/retail_data/order_items/part-m-00000.avro
-rw-r–r-- 3 nagellarajashyam hdfs 746088 2016-12-15 04:12 /user/nagellarajashyam/retail_data/order_items/part-m-00001.avro
drwxr-xr-x - nagellarajashyam hdfs 0 2016-12-15 04:12 /user/nagellarajashyam/retail_data/orders
-rw-r–r-- 3 nagellarajashyam hdfs 0 2016-12-15 04:12 /user/nagellarajashyam/retail_data/orders/_SUCCESS
-rw-r–r-- 3 nagellarajashyam hdfs 324326 2016-12-15 04:12 /user/nagellarajashyam/retail_data/orders/part-m-00000.avro
-rw-r–r-- 3 nagellarajashyam hdfs 329255 2016-12-15 04:12 /user/nagellarajashyam/retail_data/orders/part-m-00001.avro
drwxr-xr-x - nagellarajashyam hdfs 0 2016-12-15 04:13 /user/nagellarajashyam/retail_data/products
-rw-r–r-- 3 nagellarajashyam hdfs 0 2016-12-15 04:13 /user/nagellarajashyam/retail_data/products/_SUCCESS
-rw-r–r-- 3 nagellarajashyam hdfs 23126 2016-12-15 04:13 /user/nagellarajashyam/retail_data/products/part-m-00000.avro
-rw-r–r-- 3 nagellarajashyam hdfs 28371 2016-12-15 04:13 /user/nagellarajashyam/retail_data/products/part-m-00001.avro

=================================================

hive> describe formatted orders_sqoop_import;
OK

col_name data_type comment

order_id int
order_date string
order_customer_id int
order_status string

Detailed Table Information

Database: nagellarajashyam
Owner: nagellarajashyam
CreateTime: Thu Dec 15 05:24:00 EST 2016
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://nn01.itversity.com:8020/apps/hive/warehouse/nagellarajashyam.db/orders_sqoop_import
Table Type: MANAGED_TABLE
Table Parameters:
comment Imported by sqoop on 2016/12/15 05:23:57
numFiles 1
numRows 0
rawDataSize 0
totalSize 2999944
transient_lastDdlTime 1481797441

Storage Information

SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
field.delim |
line.delim \n
serialization.format |
Time taken: 0.349 seconds, Fetched: 36 row(s)

============================================================

mysql> select * from ecomm_join limit 10;
±----------------------±----------------±-------------------+
| order_date | department_name | revenue_total |
±----------------------±----------------±-------------------+
| 2013-07-26 00:00:00.0 | Footwear | 5129.419999999998 |
| 2013-07-26 00:00:00.0 | Fitness | 183.98000000000002 |
| 2013-07-26 00:00:00.0 | Fan Shop | 20847.67999999999 |
| 2013-07-26 00:00:00.0 | Apparel | 8828.749999999993 |
| 2013-07-25 00:00:00.0 | Outdoors | 627.8 |
| 2013-07-25 00:00:00.0 | Golf | 2029.72 |
| 2013-07-25 00:00:00.0 | Footwear | 3899.609999999999 |
| 2013-07-25 00:00:00.0 | Fitness | 394.92999999999995 |
| 2013-07-25 00:00:00.0 | Fan Shop | 9798.689999999997 |
| 2013-07-25 00:00:00.0 | Apparel | 3279.5699999999997 |

0 Likes

#6

Output of hadoop fs -ls -R /user/YOUR_USER_NAME/retail_data

[farhanmisarwala@gw01 ~]$ hadoop fs -ls -R /user/farhanmisarwala/data/retail_data
drwxr-xr-x   - farhanmisarwala hdfs          0 2016-12-15 04:47 /user/farhanmisarwala/data/retail_data/categories
-rw-r--r--   3 farhanmisarwala hdfs          0 2016-12-15 04:47 /user/farhanmisarwala/data/retail_data/categories/_SUCCESS
-rw-r--r--   3 farhanmisarwala hdfs        989 2016-12-15 04:47 /user/farhanmisarwala/data/retail_data/categories/part-m-00000.avro
-rw-r--r--   3 farhanmisarwala hdfs        963 2016-12-15 04:47 /user/farhanmisarwala/data/retail_data/categories/part-m-00001.avro
drwxr-xr-x   - farhanmisarwala hdfs          0 2016-12-15 04:48 /user/farhanmisarwala/data/retail_data/customers
-rw-r--r--   3 farhanmisarwala hdfs          0 2016-12-15 04:48 /user/farhanmisarwala/data/retail_data/customers/_SUCCESS
-rw-r--r--   3 farhanmisarwala hdfs     216186 2016-12-15 04:48 /user/farhanmisarwala/data/retail_data/customers/part-m-00000.avro
-rw-r--r--   3 farhanmisarwala hdfs     218877 2016-12-15 04:48 /user/farhanmisarwala/data/retail_data/customers/part-m-00001.avro
drwxr-xr-x   - farhanmisarwala hdfs          0 2016-12-15 04:48 /user/farhanmisarwala/data/retail_data/departments
-rw-r--r--   3 farhanmisarwala hdfs          0 2016-12-15 04:48 /user/farhanmisarwala/data/retail_data/departments/_SUCCESS
-rw-r--r--   3 farhanmisarwala hdfs        479 2016-12-15 04:48 /user/farhanmisarwala/data/retail_data/departments/part-m-00000.avro
-rw-r--r--   3 farhanmisarwala hdfs        427 2016-12-15 04:48 /user/farhanmisarwala/data/retail_data/departments/part-m-00001.avro
drwxr-xr-x   - farhanmisarwala hdfs          0 2016-12-15 04:48 /user/farhanmisarwala/data/retail_data/order_items
-rw-r--r--   3 farhanmisarwala hdfs          0 2016-12-15 04:48 /user/farhanmisarwala/data/retail_data/order_items/_SUCCESS
-rw-r--r--   3 farhanmisarwala hdfs     751458 2016-12-15 04:48 /user/farhanmisarwala/data/retail_data/order_items/part-m-00000.avro
-rw-r--r--   3 farhanmisarwala hdfs     746088 2016-12-15 04:48 /user/farhanmisarwala/data/retail_data/order_items/part-m-00001.avro
drwxr-xr-x   - farhanmisarwala hdfs          0 2016-12-15 04:49 /user/farhanmisarwala/data/retail_data/orders
-rw-r--r--   3 farhanmisarwala hdfs          0 2016-12-15 04:49 /user/farhanmisarwala/data/retail_data/orders/_SUCCESS
-rw-r--r--   3 farhanmisarwala hdfs     324326 2016-12-15 04:49 /user/farhanmisarwala/data/retail_data/orders/part-m-00000.avro
-rw-r--r--   3 farhanmisarwala hdfs     329255 2016-12-15 04:49 /user/farhanmisarwala/data/retail_data/orders/part-m-00001.avro
drwxr-xr-x   - farhanmisarwala hdfs          0 2016-12-15 04:49 /user/farhanmisarwala/data/retail_data/products
-rw-r--r--   3 farhanmisarwala hdfs          0 2016-12-15 04:49 /user/farhanmisarwala/data/retail_data/products/_SUCCESS
-rw-r--r--   3 farhanmisarwala hdfs      23126 2016-12-15 04:49 /user/farhanmisarwala/data/retail_data/products/part-m-00000.avro
-rw-r--r--   3 farhanmisarwala hdfs      28371 2016-12-15 04:49 /user/farhanmisarwala/data/retail_data/products/part-m-00001.avro

Output of describe formatted orders_sqooped

hive> describe formatted orders_sqooped;
FAILED: SemanticException [Error 10001]: Table not found orders_sqooped
hive> describe formatted orders_scooped;
OK
# col_name              data_type               comment

order_id                int
order_date              string
order_customer_id       int
order_status            string

# Detailed Table Information
Database:               farhan
Owner:                  farhanmisarwala
CreateTime:             Thu Dec 15 05:18:02 EST 2016
LastAccessTime:         UNKNOWN
Protect Mode:           None
Retention:              0
Location:               hdfs://nn01.itversity.com:8020/apps/hive/warehouse/farhan.db/orders_scooped
Table Type:             MANAGED_TABLE
Table Parameters:
        comment                 Imported by sqoop on 2016/12/15 05:17:59
        numFiles                4
        numRows                 0
        rawDataSize             0
        totalSize               2999944
        transient_lastDdlTime   1481797083

# Storage Information
SerDe Library:          org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat:            org.apache.hadoop.mapred.TextInputFormat
OutputFormat:           org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed:             No
Num Buckets:            -1
Bucket Columns:         []
Sort Columns:           []
Storage Desc Params:
        field.delim             |
        line.delim              \n
        serialization.format    |
Time taken: 0.374 seconds, Fetched: 36 row(s)

Output of select * from retail_export.YOUR_TABLE_NAME limit 10

mysql> select * from dep_rev_per_day_farhan limit 10;
+-----------------------+-----------------+--------------------+
| order_date            | department_name | revenue_per_day    |
+-----------------------+-----------------+--------------------+
| 2013-07-25 00:00:00.0 | Apparel         | 3279.5701179504395 |
| 2013-07-25 00:00:00.0 | Fan Shop        |   9798.69021987915 |
| 2013-07-25 00:00:00.0 | Fitness         | 394.92999267578125 |
| 2013-07-25 00:00:00.0 | Footwear        | 3899.6100540161133 |
| 2013-07-25 00:00:00.0 | Golf            |  2029.720012664795 |
| 2013-07-25 00:00:00.0 | Outdoors        |   627.799991607666 |
| 2013-07-26 00:00:00.0 | Apparel         |  8828.750286102295 |
| 2013-07-26 00:00:00.0 | Fan Shop        |  20847.68049621582 |
| 2013-07-26 00:00:00.0 | Fitness         |  183.9800033569336 |
| 2013-07-26 00:00:00.0 | Footwear        |  5129.419956207275 |
+-----------------------+-----------------+--------------------+
10 rows in set (0.00 sec)
0 Likes

#7

Output of hadoop fs -ls -R /user/YOUR_USER_NAME/retail_data

hadoop fs -ls -R /user/parulshine92/retail_data
drwxr-xr-x - parulshine92 hdfs 0 2016-12-15 04:55 /user/parulshine92/retail_data/categories
-rw-r–r-- 3 parulshine92 hdfs 0 2016-12-15 04:55 /user/parulshine92/retail_data/categories/_SUCCESS
-rw-r–r-- 3 parulshine92 hdfs 856 2016-12-15 04:55 /user/parulshine92/retail_data/categories/part-m-00000.avro
-rw-r–r-- 3 parulshine92 hdfs 838 2016-12-15 04:55 /user/parulshine92/retail_data/categories/part-m-00001.avro
drwxr-xr-x - parulshine92 hdfs 0 2016-12-15 04:56 /user/parulshine92/retail_data/customers
-rw-r–r-- 3 parulshine92 hdfs 0 2016-12-15 04:56 /user/parulshine92/retail_data/customers/_SUCCESS
-rw-r–r-- 3 parulshine92 hdfs 154461 2016-12-15 04:56 /user/parulshine92/retail_data/customers/part-m-00000.avro
-rw-r–r-- 3 parulshine92 hdfs 156051 2016-12-15 04:56 /user/parulshine92/retail_data/customers/part-m-00001.avro
drwxr-xr-x - parulshine92 hdfs 0 2016-12-15 04:56 /user/parulshine92/retail_data/departments
-rw-r–r-- 3 parulshine92 hdfs 0 2016-12-15 04:56 /user/parulshine92/retail_data/departments/_SUCCESS
-rw-r–r-- 3 parulshine92 hdfs 474 2016-12-15 04:56 /user/parulshine92/retail_data/departments/part-m-00000.avro
-rw-r–r-- 3 parulshine92 hdfs 424 2016-12-15 04:56 /user/parulshine92/retail_data/departments/part-m-00001.avro
drwxr-xr-x - parulshine92 hdfs 0 2016-12-15 04:56 /user/parulshine92/retail_data/order_items
-rw-r–r-- 3 parulshine92 hdfs 0 2016-12-15 04:56 /user/parulshine92/retail_data/order_items/_SUCCESS
-rw-r–r-- 3 parulshine92 hdfs 404911 2016-12-15 04:56 /user/parulshine92/retail_data/order_items/part-m-00000.avro
-rw-r–r-- 3 parulshine92 hdfs 397137 2016-12-15 04:56 /user/parulshine92/retail_data/order_items/part-m-00001.avro
drwxr-xr-x - parulshine92 hdfs 0 2016-12-15 04:57 /user/parulshine92/retail_data/orders
-rw-r–r-- 3 parulshine92 hdfs 0 2016-12-15 04:57 /user/parulshine92/retail_data/orders/_SUCCESS
-rw-r–r-- 3 parulshine92 hdfs 200569 2016-12-15 04:57 /user/parulshine92/retail_data/orders/part-m-00000.avro
-rw-r–r-- 3 parulshine92 hdfs 204616 2016-12-15 04:57 /user/parulshine92/retail_data/orders/part-m-00001.avro
drwxr-xr-x - parulshine92 hdfs 0 2016-12-15 04:57 /user/parulshine92/retail_data/products
-rw-r–r-- 3 parulshine92 hdfs 0 2016-12-15 04:57 /user/parulshine92/retail_data/products/_SUCCESS
-rw-r–r-- 3 parulshine92 hdfs 15746 2016-12-15 04:57 /user/parulshine92/retail_data/products/part-m-00000.avro
-rw-r–r-- 3 parulshine92 hdfs 19248 2016-12-15 04:57 /user/parulshine92/retail_data/products/part-m-00001.avro


hive> describe formatted orders_sqooped;
OK

col_name data_type comment

order_id int
order_date string
order_customer_id int
order_status string

Detailed Table Information

Database: default
Owner: infosnehasish
CreateTime: Thu Dec 15 04:23:05 EST 2016
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://nn01.itversity.com:8020/apps/hive/warehouse/orders_sqooped
Table Type: MANAGED_TABLE
Table Parameters:
comment Imported by sqoop on 2016/12/15 04:23:02
numFiles 12
numRows 0
rawDataSize 0
totalSize 8999832
transient_lastDdlTime 1481796392

Storage Information

SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
field.delim |
line.delim \n
serialization.format |
Time taken: 2.69 seconds, Fetched: 36 row(s)

Output of select * from retail_export.YOUR_TABLE_NAME limit 10

select * from revenue limit 10;
±----------------------±----------------±-------------------+
| order_date | department_name | revenue_per_day |
±----------------------±----------------±-------------------+
| 2013-10-23 00:00:00.0 | Apparel | 7319.060234069824 |
| 2013-10-23 00:00:00.0 | Fan Shop | 18538.100467681885 |
| 2013-10-23 00:00:00.0 | Fitness | 214.93000030517578 |
| 2013-10-23 00:00:00.0 | Footwear | 7159.470001220703 |
| 2013-10-23 00:00:00.0 | Golf | 5974.700012207031 |
| 2013-10-23 00:00:00.0 | Outdoors | 1846.5599670410156 |
| 2013-10-24 00:00:00.0 | Apparel | 4089.470142364502 |
| 2013-10-24 00:00:00.0 | Fan Shop | 11348.930282592773 |
| 2013-10-24 00:00:00.0 | Fitness | 349.95001220703125 |
| 2013-10-24 00:00:00.0 | Footwear | 1399.859992980957 |
±----------------------±----------------±-------------------+
10 rows in set (0.00 sec)

0 Likes

#8

Directory for Sqoop Import All Table:

[jasonbourne@gw01 ~]$ hadoop fs -ls -R /user/jasonbourne/retail_db_data
drwxr-xr-x - jasonbourne jasonbourne 0 2016-12-15 02:07 /user/jasonbourne/retail_db_data/categories
-rw-r–r-- 3 jasonbourne jasonbourne 0 2016-12-15 02:07 /user/jasonbourne/retail_db_data/categories/_SUCCESS
-rw-r–r-- 3 jasonbourne jasonbourne 271 2016-12-15 02:07 /user/jasonbourne/retail_db_data/categories/part-m-00000
-rw-r–r-- 3 jasonbourne jasonbourne 263 2016-12-15 02:07 /user/jasonbourne/retail_db_data/categories/part-m-00001
-rw-r–r-- 3 jasonbourne jasonbourne 266 2016-12-15 02:07 /user/jasonbourne/retail_db_data/categories/part-m-00002
-rw-r–r-- 3 jasonbourne jasonbourne 229 2016-12-15 02:07 /user/jasonbourne/retail_db_data/categories/part-m-00003
drwxr-xr-x - jasonbourne jasonbourne 0 2016-12-15 02:07 /user/jasonbourne/retail_db_data/customers
-rw-r–r-- 3 jasonbourne jasonbourne 0 2016-12-15 02:07 /user/jasonbourne/retail_db_data/customers/_SUCCESS
-rw-r–r-- 3 jasonbourne jasonbourne 237145 2016-12-15 02:07 /user/jasonbourne/retail_db_data/customers/part-m-00000
-rw-r–r-- 3 jasonbourne jasonbourne 237965 2016-12-15 02:07 /user/jasonbourne/retail_db_data/customers/part-m-00001
-rw-r–r-- 3 jasonbourne jasonbourne 238092 2016-12-15 02:07 /user/jasonbourne/retail_db_data/customers/part-m-00002
-rw-r–r-- 3 jasonbourne jasonbourne 240323 2016-12-15 02:07 /user/jasonbourne/retail_db_data/customers/part-m-00003
drwxr-xr-x - jasonbourne jasonbourne 0 2016-12-15 02:07 /user/jasonbourne/retail_db_data/departments
-rw-r–r-- 3 jasonbourne jasonbourne 0 2016-12-15 02:07 /user/jasonbourne/retail_db_data/departments/_SUCCESS
-rw-r–r-- 3 jasonbourne jasonbourne 64 2016-12-15 02:07 /user/jasonbourne/retail_db_data/departments/part-m-0000 0
-rw-r–r-- 3 jasonbourne jasonbourne 0 2016-12-15 02:07 /user/jasonbourne/retail_db_data/departments/part-m-0000 1
-rw-r–r-- 3 jasonbourne jasonbourne 0 2016-12-15 02:07 /user/jasonbourne/retail_db_data/departments/part-m-0000 2
-rw-r–r-- 3 jasonbourne jasonbourne 20 2016-12-15 02:07 /user/jasonbourne/retail_db_data/departments/part-m-0000 3
drwxr-xr-x - jasonbourne jasonbourne 0 2016-12-15 02:08 /user/jasonbourne/retail_db_data/order_items
-rw-r–r-- 3 jasonbourne jasonbourne 0 2016-12-15 02:08 /user/jasonbourne/retail_db_data/order_items/_SUCCESS
-rw-r–r-- 3 jasonbourne jasonbourne 1303818 2016-12-15 02:08 /user/jasonbourne/retail_db_data/order_items/part-m-0000 0
-rw-r–r-- 3 jasonbourne jasonbourne 1343222 2016-12-15 02:08 /user/jasonbourne/retail_db_data/order_items/part-m-0000 1
-rw-r–r-- 3 jasonbourne jasonbourne 1371917 2016-12-15 02:08 /user/jasonbourne/retail_db_data/order_items/part-m-0000 2
-rw-r–r-- 3 jasonbourne jasonbourne 1389923 2016-12-15 02:08 /user/jasonbourne/retail_db_data/order_items/part-m-0000 3
drwxr-xr-x - jasonbourne jasonbourne 0 2016-12-15 02:08 /user/jasonbourne/retail_db_data/orders
-rw-r–r-- 3 jasonbourne jasonbourne 0 2016-12-15 02:08 /user/jasonbourne/retail_db_data/orders/_SUCCESS
-rw-r–r-- 3 jasonbourne jasonbourne 741614 2016-12-15 02:08 /user/jasonbourne/retail_db_data/orders/part-m-00000
-rw-r–r-- 3 jasonbourne jasonbourne 753022 2016-12-15 02:08 /user/jasonbourne/retail_db_data/orders/part-m-00001
-rw-r–r-- 3 jasonbourne jasonbourne 752368 2016-12-15 02:08 /user/jasonbourne/retail_db_data/orders/part-m-00002
-rw-r–r-- 3 jasonbourne jasonbourne 752940 2016-12-15 02:08 /user/jasonbourne/retail_db_data/orders/part-m-00003
drwxr-xr-x - jasonbourne jasonbourne 0 2016-12-15 02:08 /user/jasonbourne/retail_db_data/products
-rw-r–r-- 3 jasonbourne jasonbourne 0 2016-12-15 02:08 /user/jasonbourne/retail_db_data/products/_SUCCESS
-rw-r–r-- 3 jasonbourne jasonbourne 41419 2016-12-15 02:08 /user/jasonbourne/retail_db_data/products/part-m-00000
-rw-r–r-- 3 jasonbourne jasonbourne 43660 2016-12-15 02:08 /user/jasonbourne/retail_db_data/products/part-m-00001
-rw-r–r-- 3 jasonbourne jasonbourne 42195 2016-12-15 02:08 /user/jasonbourne/retail_db_data/products/part-m-00002
-rw-r–r-- 3 jasonbourne jasonbourne 46719 2016-12-15 02:08 /user/jasonbourne/retail_db_data/products/part-m-00003

describe formatted orders_sqooped:

hive> describe formatted orders_sqooped;
OK

col_name data_type comment

order_id int
order_date string
order_customer_id int
order_status string

Detailed Table Information

Database: jasonbourne
Owner: jasonbourne
CreateTime: Thu Dec 15 05:15:28 EST 2016
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://nn01.itversity.com:8020/apps/hive/warehouse/jasonbourne.db/orders_sqooped
Table Type: MANAGED_TABLE
Table Parameters:
COLUMN_STATS_ACCURATE {“BASIC_STATS”:“true”}
comment Imported by sqoop on 2016/12/15 05:15:25
numFiles 0
numRows 0
rawDataSize 0
totalSize 0
transient_lastDdlTime 1481796928

Storage Information

SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
field.delim |
line.delim \n
serialization.format |

Selecting from exported table:

mysql> select * from retail_export_jason_rdpd limit 10;
±--------------------±----------------±--------+
| order_date | department_name | revenue |
±--------------------±----------------±--------+
| 2014-04-24 00:00:00 | Golf | 2829.85 |
| 2014-04-24 00:00:00 | Outdoors | 821.72 |
| 2014-04-25 00:00:00 | Apparel | 9438.73 |
| 2014-04-25 00:00:00 | Fan Shop | 20197.6 |
| 2014-04-25 00:00:00 | Fitness | 284.93 |
| 2014-04-25 00:00:00 | Footwear | 3695.61 |
| 2014-04-25 00:00:00 | Golf | 5699.65 |
| 2014-04-25 00:00:00 | Outdoors | 1243.55 |
| 2014-04-26 00:00:00 | Apparel | 9698.71 |
| 2014-04-26 00:00:00 | Fan Shop | 22094 |
±--------------------±----------------±--------+

mysql> select count() from retail_export_jason_rdpd;
±---------+
| count(
) |
±---------+
| 2116 |
±---------+

0 Likes

#9

col_name data_type comment

order_id int
order_date string
order_customer_id int
order_status string

Detailed Table Information

Database: paramesh
Owner: paramesh
CreateTime: Thu Dec 15 06:14:04 EST 2016
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://nn01.itversity.com:8020/apps/hive/warehouse/paramesh.db/orders_sqoop
Table Type: MANAGED_TABLE
Table Parameters:
comment Imported by sqoop on 2016/12/15 06:14:01
numFiles 4
numRows 0
rawDataSize 0
totalSize 2999944
transient_lastDdlTime 1481800445

Storage Information

SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
field.delim |
line.delim \n
serialization.format |
Time taken: 0.363 seconds, Fetched: 36 row(s)

drwxr-xr-x - paramesh paramesh 0 2016-12-15 05:40 /user/paramesh/retail_data1/categories
drwxr-xr-x - paramesh paramesh 0 2016-12-15 05:41 /user/paramesh/retail_data1/customers
drwxr-xr-x - paramesh paramesh 0 2016-12-15 05:41 /user/paramesh/retail_data1/departments
drwxr-xr-x - paramesh paramesh 0 2016-12-15 05:41 /user/paramesh/retail_data1/order_items
drwxr-xr-x - paramesh paramesh 0 2016-12-15 05:42 /user/paramesh/retail_data1/orders
drwxr-xr-x - paramesh paramesh 0 2016-12-15 05:42 /user/paramesh/retail_data1/products

0 Likes

#10

Importing all tables:

sqoop import-all-tables -Dmapreduce.job.user.classpath.first=true --connect jdbc:mysql://nn01.itversity.com/retail_db
–username retail_dba -P --warehouse-dir /user/mahesh007/retail_data -z --as-avrodatafile -m 2

[mahesh007@gw01 ~]$ hadoop fs -ls -R /user/mahesh007/retail_data
drwxr-xr-x - mahesh007 hdfs 0 2016-12-15 05:23 /user/mahesh007/retail_data/categories
-rw-r–r-- 3 mahesh007 hdfs 0 2016-12-15 05:23 /user/mahesh007/retail_data/categories/_SUCCESS
-rw-r–r-- 3 mahesh007 hdfs 856 2016-12-15 05:23 /user/mahesh007/retail_data/categories/part-m-00000.avr
-rw-r–r-- 3 mahesh007 hdfs 838 2016-12-15 05:23 /user/mahesh007/retail_data/categories/part-m-00001.avr
drwxr-xr-x - mahesh007 hdfs 0 2016-12-15 05:23 /user/mahesh007/retail_data/customers
-rw-r–r-- 3 mahesh007 hdfs 0 2016-12-15 05:23 /user/mahesh007/retail_data/customers/_SUCCESS
-rw-r–r-- 3 mahesh007 hdfs 154461 2016-12-15 05:23 /user/mahesh007/retail_data/customers/part-m-00000.avro
-rw-r–r-- 3 mahesh007 hdfs 156051 2016-12-15 05:23 /user/mahesh007/retail_data/customers/part-m-00001.avro
drwxr-xr-x - mahesh007 hdfs 0 2016-12-15 05:24 /user/mahesh007/retail_data/departments
-rw-r–r-- 3 mahesh007 hdfs 0 2016-12-15 05:24 /user/mahesh007/retail_data/departments/_SUCCESS
-rw-r–r-- 3 mahesh007 hdfs 474 2016-12-15 05:24 /user/mahesh007/retail_data/departments/part-m-00000.av
-rw-r–r-- 3 mahesh007 hdfs 424 2016-12-15 05:24 /user/mahesh007/retail_data/departments/part-m-00001.av
drwxr-xr-x - mahesh007 hdfs 0 2016-12-15 05:24 /user/mahesh007/retail_data/order_items
-rw-r–r-- 3 mahesh007 hdfs 0 2016-12-15 05:24 /user/mahesh007/retail_data/order_items/_SUCCESS
-rw-r–r-- 3 mahesh007 hdfs 404911 2016-12-15 05:24 /user/mahesh007/retail_data/order_items/part-m-00000.av
-rw-r–r-- 3 mahesh007 hdfs 397137 2016-12-15 05:24 /user/mahesh007/retail_data/order_items/part-m-00001.av
drwxr-xr-x - mahesh007 hdfs 0 2016-12-15 05:24 /user/mahesh007/retail_data/orders
-rw-r–r-- 3 mahesh007 hdfs 0 2016-12-15 05:24 /user/mahesh007/retail_data/orders/_SUCCESS
-rw-r–r-- 3 mahesh007 hdfs 200569 2016-12-15 05:24 /user/mahesh007/retail_data/orders/part-m-00000.avro
-rw-r–r-- 3 mahesh007 hdfs 204616 2016-12-15 05:24 /user/mahesh007/retail_data/orders/part-m-00001.avro
drwxr-xr-x - mahesh007 hdfs 0 2016-12-15 05:25 /user/mahesh007/retail_data/products
-rw-r–r-- 3 mahesh007 hdfs 0 2016-12-15 05:25 /user/mahesh007/retail_data/products/_SUCCESS
-rw-r–r-- 3 mahesh007 hdfs 15746 2016-12-15 05:25 /user/mahesh007/retail_data/products/part-m-00000.avro
-rw-r–r-- 3 mahesh007 hdfs 19248 2016-12-15 05:25 /user/mahesh007/retail_data/products/part-m-00001.avro

Hive import:

sqoop import --connect jdbc:mysql://nn01.itversity.com/retail_db --username retail_dba -P
–table orders --fields-terminated-by “|”
–hive-import
–create-hive-table
–hive-table mahesh007.orders_sqooped

describe formatted orders_sqooped;

col_name data_type comment

col_name data_type comment

“” null null
order_id int ""
order_date string ""
order_customer_id int ““
order_status string “”
”” null null

Detailed Table Information null null

Database: mahesh007 null
Owner: mahesh007 null
CreateTime: Thu Dec 15 05:45:49 EST 2016 null
LastAccessTime: UNKNOWN null
Protect Mode: None null
Retention: 0 null
Location: hdfs://nn01.itversity.com:8020/apps/hive/warehouse/mahesh007.db/orders_sqooped null
Table Type: MANAGED_TABLE null
Table Parameters: null null
"" comment Imported by sqoop on 2016/12/15 05:45:46
"" numFiles 4
"" numRows 0
"" rawDataSize 0
"" totalSize 2999944
"" transient_lastDdlTime 1481798749
"" null null

Storage Information null null

SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe null
InputFormat: org.apache.hadoop.mapred.TextInputFormat null
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat null
Compressed: No null
Num Buckets: -1 null
Bucket Columns: [] null
Sort Columns: [] null
Storage Desc Params: null null
"" field.delim |
"" line.delim \n
"" serialization.format |

mysql> select * from dept_revenue limit 10;
±--------------------±----------------±-------------------+
| order_date | department_name | revenue_per_day |
±--------------------±----------------±-------------------+
| 2013-10-22 00:00:00 | Fitness | 174.98999786376953 |
| 2013-10-22 00:00:00 | Footwear | 99.98999786376953 |
| 2013-10-23 00:00:00 | Apparel | 7319.060234069824 |
| 2013-10-23 00:00:00 | Fan Shop | 13048.490268707275 |
| 2013-10-23 00:00:00 | Fitness | 124.94999694824219 |
| 2013-10-23 00:00:00 | Footwear | 2399.97998046875 |
| 2013-10-24 00:00:00 | Apparel | 4089.470142364502 |
| 2013-10-24 00:00:00 | Fan Shop | 9549.050216674805 |
| 2013-10-24 00:00:00 | Fitness | 50 |
| 2013-10-25 00:00:00 | Apparel | 5719.2802085876465 |
±--------------------±----------------±-------------------+
10 rows in set (0.00 sec)

0 Likes

#11

[aruncse11@gw01 ~]$ hdfs dfs -ls /user/aruncse11/arun/retail_data
Found 6 items
drwxr-xr-x - aruncse11 hdfs 0 2016-12-15 05:26 /user/aruncse11/arun/retail_data/categories
drwxr-xr-x - aruncse11 hdfs 0 2016-12-15 05:26 /user/aruncse11/arun/retail_data/customers
drwxr-xr-x - aruncse11 hdfs 0 2016-12-15 05:26 /user/aruncse11/arun/retail_data/departments
drwxr-xr-x - aruncse11 hdfs 0 2016-12-15 05:27 /user/aruncse11/arun/retail_data/order_items
drwxr-xr-x - aruncse11 hdfs 0 2016-12-15 05:27 /user/aruncse11/arun/retail_data/orders
drwxr-xr-x - aruncse11 hdfs 0 2016-12-15 05:27 /user/aruncse11/arun/retail_data/products

hive> describe formatted orders_sqooped;
OK

col_name data_type comment

order_id int
order_date string
order_customer_id int
order_status string

Detailed Table Information

Database: arun
Owner: aruncse11
CreateTime: Thu Dec 15 05:49:07 EST 2016
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://nn01.itversity.com:8020/apps/hive/warehouse/arun.db/orders_sqooped
Table Type: MANAGED_TABLE
Table Parameters:
comment Imported by sqoop on 2016/12/15 05:49:04
numFiles 4
numRows 0
rawDataSize 0
totalSize 2999944
transient_lastDdlTime 1481798948

Storage Information

SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
field.delim |
line.delim \n
serialization.format |
Time taken: 0.367 seconds, Fetched: 36 row(s)

mysql> select * from dep_revenueArun limit 3;
±----------------------±----------------±-------------------+
| order_date | department_name | revenue_per_day |
±----------------------±----------------±-------------------+
| 2013-07-25 00:00:00.0 | Apparel | 3279.5701179504395 |
| 2013-07-25 00:00:00.0 | Fan Shop | 9798.69021987915 |
| 2013-07-25 00:00:00.0 | Fitness | 394.92999267578125 |
±----------------------±----------------±-------------------+
3

0 Likes