将mysql中的emp_add表的结构复制到hive中sqoop create-hive-table \--connect jdbc:mysql://node01:3306/userdb \--table emp_add \--username root \--password hadoop \--hive-table hivedatabase.emp_add_sp注：  --table emp_add为mysql中的数据库userdb中的表。         --hive-table emp_add_sp为hive中hivedatabase库中新建的表名称

将MySQL数据库 userdb 中 emp_add 表的数据导入到Hive表中sqoop import \--connect jdbc:mysql://node01:3306/userdb \--table emp_add \--username root \--password hadoop \--hive-table emp_add \--hive-import

将MySQL数据库 userdb 中 emp_conn 表的结构以及数据导入到Hive的hivedatabase库中sqoop import \--connect jdbc:mysql://node01:3306/userdb \--username root \--password hadoop \--table emp_conn \--hive-import \--m 1 \--hive-database hivedatabase

--where可以指定从关系数据库导入数据时的查询条件；它执行在数据库服务器相应的SQL查询，并将结果存储在HDFS的目标目录sqoop import \--connect jdbc:mysql://node01:3306/userdb \--table emp_add --m 1 \--where "city ='sec-bad'" \--username root \--password hadoop \--target-dir /wherequery

注意事项：使用query sql语句来进行查找不能加参数--table ;并且必须要添加where条件;并且where条件后面必须带一个$CONDITIONS 这个字符串;并且这个sql语句必须用单引号，不能用双引号;sqoop命令中，--split-by id 通常配合-m 10参数使用；用于指定根据哪个字段进行划分并启动多少个maptask。sqoop import \--connect jdbc:mysql://node01:3306/userdb \--username root \--password hadoop \--target-dir /wherequery2 \--query 'select id,name,deg from emp WHERE id>1203 and $CONDITIONS' \--split-by id \--fields-terminated-by '\t' \--m 2

sqoop import \--connect jdbc:mysql://node01:3306/userdb \--table emp --m 1 \--username root \--password hadoop \--target-dir /appendresult \--incremental append \--check-column id \--last-value  1205注：--check-column (col)        用来指定一些列，这些列在增量导入时用来检查这些数据是否作为增量数据进行导入，和关系型数据库中的自增字段及时间戳类似这些被指定的列的类型不能使任意字符类型，如char、varchar等类型都是不可以的，同时-- check-column可以去指定多个列--incremental (mode)    append：追加，比如对大于last-value指定的值之后的记录进行追加导入；lastmodified：最后的修改时间，追加last-value指定的日期之后的记录--last-value (value)指定自从上次导入后列的最大值（大于该指定的值），也可以自己设定某一值

append模式：sqoop import \--connect jdbc:mysql://node01:3306/userdb \--table customertest \--username root \--password hadoop \--target-dir /lastmodifiedresult \--check-column last_mod \--incremental lastmodified \--last-value "2019-05-28 18:42:06" \--m 1 \--appendmerge-key模式：sqoop import \--connect jdbc:mysql://node01:3306/userdb \--username root \--password hadoop \--table customertest \--target-dir /lastmodifiedresult \--check-column last_mod \--incremental lastmodified \--last-value "2019-05-28 18:42:06" \--m 1 \--merge-key id注:lastmodified模式去处理增量时，会将大于等于last-value值的数据当做增量插入

logging.level.root=infospring.datasource.driver-class-name=com.mysql.jdbc.Driverspring.datasource.url=jdbc:mysql://node01:3306/gmall?characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8spring.datasource.username=rootspring.datasource.password=hadooplogging.pattern.console=%m%nmybatis-plus.global-config.db-config.field-strategy=not_null#业务日期mock.date=2020-03-10#是否重置mock.clear=1#是否生成新用户mock.user.count=50#男性比例mock.user.male-rate=20#收藏取消比例mock.favor.cancel-rate=10#收藏数量mock.favor.count=100#购物车数量mock.cart.count=10#每个商品最多购物个数mock.cart.sku-maxcount-per-cart=3#用户下单比例mock.order.user-rate=80#用户从购物中购买商品比例mock.order.sku-rate=70#是否参加活动mock.order.join-activity=1#是否使用购物券mock.order.use-coupon=1#购物券领取人数mock.coupon.user-count=10#支付比例mock.payment.rate=70#支付方式 支付宝：微信 ：银联mock.payment.payment-type=30:60:10#评价比例 好：中：差：自动mock.comment.appraise-rate=30:10:10:50#退款原因比例：质量问题 商品描述与实际描述不一致 缺货 号码不合适 拍错 不想买了 其他mock.refund.reason-rate=30:10:20:5:15:5:5

java -jar gmall-mock-db-2020-03-16-SNAPSHOT.jar

mock.date=2020-03-11mock.clear=0

java -jar gmall-mock-db-2020-03-16-SNAPSHOT.jar

vim mysql_to_hdfs.sh

vim mysql_to_hdfs.sh

#! /bin/bashsqoop=/opt/modules/sqoop/bin/sqoopdo_date=`date -d '-1 day' +%F`if [[ -n "$2" ]]; thendo_date=$2fiimport_data(){$sqoop import \--connect jdbc:mysql://node01:3306/gmall \--username root \--password hadoop \--target-dir /origin_data/gmall/db/$1/$do_date \--delete-target-dir \--query "$2 and \$CONDITIONS" \--num-mappers 1 \--fields-terminated-by '\t' \--compress \--compression-codec lzop \--null-string '\\N' \--null-non-string '\\N'hadoop jar /opt/modules/hadoop/share/hadoop/common/hadoop-lzo-0.4.20.jar com.hadoop.compression.lzo.DistributedLzoIndexer /origin_data/gmall/db/$1/$do_date}import_order_info(){import_data order_info "selectid,final_total_amount,order_status,user_id,out_trade_no,create_time,operate_time,province_id,benefit_reduce_amount,original_total_amount,feight_feefrom order_infowhere (date_format(create_time,'%Y-%m-%d')='$do_date'or date_format(operate_time,'%Y-%m-%d')='$do_date')"}import_coupon_use(){import_data coupon_use "selectid,coupon_id,user_id,order_id,coupon_status,get_time,using_time,used_timefrom coupon_usewhere (date_format(get_time,'%Y-%m-%d')='$do_date'or date_format(using_time,'%Y-%m-%d')='$do_date'or date_format(used_time,'%Y-%m-%d')='$do_date')"}import_order_status_log(){import_data order_status_log "selectid,order_id,order_status,operate_timefrom order_status_logwheredate_format(operate_time,'%Y-%m-%d')='$do_date'"}import_activity_order(){import_data activity_order "selectid,activity_id,order_id,create_timefrom activity_orderwheredate_format(create_time,'%Y-%m-%d')='$do_date'"}import_user_info(){import_data "user_info" "selectid,name,birthday,gender,email,user_level,create_time,operate_timefrom user_infowhere (DATE_FORMAT(create_time,'%Y-%m-%d')='$do_date'or DATE_FORMAT(operate_time,'%Y-%m-%d')='$do_date')"}import_order_detail(){import_data order_detail "selectod.id,order_id,user_id,sku_id,sku_name,order_price,sku_num,od.create_timefrom order_detail odjoin order_info oion od.order_id=oi.idwhereDATE_FORMAT(od.create_time,'%Y-%m-%d')='$do_date'"}import_payment_info(){import_data "payment_info" "selectid,out_trade_no,order_id,user_id,alipay_trade_no,total_amount,subject,payment_type,payment_timefrom payment_infowhereDATE_FORMAT(payment_time,'%Y-%m-%d')='$do_date'"}import_comment_info(){import_data comment_info "selectid,user_id,sku_id,spu_id,order_id,appraise,comment_txt,create_timefrom comment_infowhere date_format(create_time,'%Y-%m-%d')='$do_date'"}import_order_refund_info(){import_data order_refund_info "selectid,user_id,order_id,sku_id,refund_type,refund_num,refund_amount,refund_reason_type,create_timefrom order_refund_infowheredate_format(create_time,'%Y-%m-%d')='$do_date'"}import_sku_info(){import_data sku_info "selectid,spu_id,price,sku_name,sku_desc,weight,tm_id,category3_id,create_timefrom sku_info where 1=1"}import_base_category1(){import_data "base_category1" "selectid,namefrom base_category1 where 1=1"}import_base_category2(){import_data "base_category2" "selectid,name,category1_idfrom base_category2 where 1=1"}import_base_category3(){import_data "base_category3" "selectid,name,category2_idfrom base_category3 where 1=1"}import_base_province(){import_data base_province "selectid,name,region_id,area_code,iso_codefrom base_provincewhere 1=1"}import_base_region(){import_data base_region "selectid,region_namefrom base_regionwhere 1=1"}import_base_trademark(){import_data base_trademark "selecttm_id,tm_namefrom base_trademarkwhere 1=1"}import_spu_info(){import_data spu_info "selectid,spu_name,category3_id,tm_idfrom spu_infowhere 1=1"}import_favor_info(){import_data favor_info "selectid,user_id,sku_id,spu_id,is_cancel,create_time,cancel_timefrom favor_infowhere 1=1"}import_cart_info(){import_data cart_info "selectid,user_id,sku_id,cart_price,sku_num,sku_name,create_time,operate_time,is_ordered,order_timefrom cart_infowhere 1=1"}import_coupon_info(){import_data coupon_info "selectid,coupon_name,coupon_type,condition_amount,condition_num,activity_id,benefit_amount,benefit_discount,create_time,range_type,spu_id,tm_id,category3_id,limit_num,operate_time,expire_timefrom coupon_infowhere 1=1"}import_activity_info(){import_data activity_info "selectid,activity_name,activity_type,start_time,end_time,create_timefrom activity_infowhere 1=1"}import_activity_rule(){import_data activity_rule "selectid,activity_id,condition_amount,condition_num,benefit_amount,benefit_discount,benefit_levelfrom activity_rulewhere 1=1"}import_base_dic(){import_data base_dic "selectdic_code,dic_name,parent_code,create_time,operate_timefrom base_dicwhere 1=1"}case $1 in"order_info")import_order_info;;"base_category1")import_base_category1;;"base_category2")import_base_category2;;"base_category3")import_base_category3;;"order_detail")import_order_detail;;"sku_info")import_sku_info;;"user_info")import_user_info;;"payment_info")import_payment_info;;"base_province")import_base_province;;"base_region")import_base_region;;"base_trademark")import_base_trademark;;"activity_info")import_activity_info;;"activity_order")import_activity_order;;"cart_info")import_cart_info;;"comment_info")import_comment_info;;"coupon_info")import_coupon_info;;"coupon_use")import_coupon_use;;"favor_info")import_favor_info;;"order_refund_info")import_order_refund_info;;"order_status_log")import_order_status_log;;"spu_info")import_spu_info;;"activity_rule")import_activity_rule;;"base_dic")import_base_dic;;"first")import_base_category1import_base_category2import_base_category3import_order_infoimport_order_detailimport_sku_infoimport_user_infoimport_payment_infoimport_base_provinceimport_base_regionimport_base_trademarkimport_activity_infoimport_activity_orderimport_cart_infoimport_comment_infoimport_coupon_useimport_coupon_infoimport_favor_infoimport_order_refund_infoimport_order_status_logimport_spu_infoimport_activity_ruleimport_base_dic;;"all")import_base_category1import_base_category2import_base_category3import_order_infoimport_order_detailimport_sku_infoimport_user_infoimport_payment_infoimport_base_trademarkimport_activity_infoimport_activity_orderimport_cart_infoimport_comment_infoimport_coupon_useimport_coupon_infoimport_favor_infoimport_order_refund_infoimport_order_status_logimport_spu_infoimport_activity_ruleimport_base_dic;;esac

[ -n 变量值 ] 判断变量的值，是否为空-- 变量的值，非空，返回 true-- 变量的值，为空，返回 false

查看 date 命令的使用，date --help

chmod 770 mysql2hdfs.sh

mysql2hdfs.sh first 2020-03-10

mysql2hdfs.sh all 2020-03-11

tar -zxf apache-hive-2.3.6-bin.tar.gz -C /opt/modules

mv apache-hive-2.3.6-bin hive

cp /opt/software/mysql-libs/mysql-connector-java-5.1.27/mysql-connector-java-5.1.27-bin.jar /opt/modules/hive/lib/

vim hive-site.xml

    
    
            
                     
      
       javax.jdo.option.ConnectionURL
                      
      
       jdbc:mysql://node01:3306/hive?createDatabaseIfNotExist=true
              
             
                     
      
       javax.jdo.option.ConnectionDriverName
                      
      
       com.mysql.jdbc.Driver
              
             
                     
      
       javax.jdo.option.ConnectionUserName
                      
      
       root
              
             
                     
      
       javax.jdo.option.ConnectionPassword
                      
      
       hadoop
              
             
                     
      
       hive.metastore.warehouse.dir
                      
      
       /user/hive/warehouse
              
             
                     
      
       hive.cli.print.header
                      
      
       true
              
             
                     
      
       hive.cli.print.current.db
                      
      
       true
              
             
                     
      
       hive.metastore.schema.verification
                      
      
       false
              
             
                     
      
       datanucleus.schema.autoCreateAll
                      
      
       true
              
             
                     
      
       hive.server2.thrift.bind.host
                      
      
       node01
              
             
                     
      
       hive.metastore.uris
                      
      
       thrift://node01:9083
              
     
    

nohup ./bin/hive --service metastore &nohup ./bin/hiveserver2 &./bin/beeline!connect jdbc:hive2://node01:10000

hdfs dfs -mkdir /tezhdfs dfs -put /opt/software/apache-tez-0.9.1-bin.tar.gz /tez

cd /opt/modules/hive/conf

    
    
            
                     
      
       tez.lib.uris
                      
      
       ${fs.defaultFS}/tez/apache-tez-0.9.1-bin.tar.gz
              
             
                     
      
       tez.use.cluster.hadoop-libs
                      
      
       true
              
             
                     
      
       tez.history.logging.service.class
                      
      
       org.apache.tez.dag.history.logging.ats.ATSHistoryLoggin        gService
              
     
    

            
     
      hive.execution.engine
             
     
      tez
     
    

create table student(id int,name string);

insert into student values(1,"zhangsan");

select * from student;

Caused by: org.apache.tez.dag.api.SessionNotRunning: TezSessionhas already shutdown. Application application_1546781144082_0005failed 2 times due to AM Container for appattempt_1546781144082_0005_000002 exited with exitCode: -103For more detailed output, check application trackingpage:http://hadoop103:8088/cluster/app/application_1546781144082_0005Then, click on links to logs of each attempt.Diagnostics: Container[pid=11116,containerID=container_1546781144082_0005_02_000001]is running beyond virtual memory limits. Current usage: 216.3 MBof 1 GB physical memory used; 2.6 GB of 2.1 GB virtual memory used.Killing container.

            
     
      yarn.nodemanager.vmem-check-enabled
             
     
      false