俗话说好记性不如烂笔头(汉语),物覚えは爛(日语),The palest ink is better than the best memory. (English)
使用Sqoop把Oracle表数据导入hive,再用cognos连接hive做数据访问
1、安装Sqoop
yum install sqoop
2、出现错误
Warning: /usr/lib/sqoop/../hcatalog does not exist! HCatalog jobs will fail. Please set $HCAT_HOME to the root of your HCatalog installation. Warning: /usr/lib/sqoop/../accumulo does not exist! Accumulo imports will fail. Please set $ACCUMULO_HOME to the root of your Accumulo installation.
解决办法:
mvo oracle驱动jar包到 /usr/lib/sqoop/lib
出现错误:
Caused by: org.apache.hadoop.ipc.RemoteException(org.apache.hadoop.security.AccessControlException): Permission denied: user=root, access=WRITE, inode="/bidw/accident":hdfs:hbase:drwxr-xr-x
解决办法:
sudo -u hdfs hdfs dfs -chmod 777 /bidw/accident
3、再次导入(通过连接oracle,把需要表导入到hdfs中)
sqoop import --connect jdbc:oracle:thin:@//localhost:1521/jgyw --username ylyp --password oracle --query "select * from vio_fact_violation where wfrqbm>0 and \$CONDITIONS " --target-dir /vio_project/vio_fact_violation --outdir /tmp/vio_violation -m 1
sqoop import --connect jdbc:oracle:thin:@//localhost:1521/jgyw --username ylyp --password oracle --query "select * from VIO_DIM_RQ where \$CONDITIONS " --target-dir /vio_project/VIO_DIM_RQ --outdir /tmp/VIO_DIM_RQ -m 1
sqoop import --connect jdbc:oracle:thin:@//localhost:1521/jgyw --username ylyp --password oracle --query "select * from VIO_DIM_FXLD where \$CONDITIONS " --target-dir /vio_project/VIO_DIM_FXLD --outdir /tmp/VIO_DIM_FXLD -m 1
sqoop import --connect jdbc:oracle:thin:@//localhost:1521/jgyw --username ylyp --password oracle --query "select * from VIO_DIM_FXCLJG where \$CONDITIONS " --target-dir /vio_project/VIO_DIM_FXCLJG --outdir /tmp/VIO_DIM_FXCLJG -m 1
sqoop import --connect jdbc:oracle:thin:@//localhost:1521/jgyw --username ylyp --password oracle --query "select * from VIO_DIM_FZJG where \$CONDITIONS " --target-dir /vio_project/VIO_DIM_FZJG --outdir /tmp/VIO_DIM_FZJG -m 1
and so on
4、通过集群管理界面查看job
http://localhost:8088/cluster
5、在Hive中创建表(数据基于步骤3上传到hdfs上的数据)
详细描述步骤参考:
利用sqoop导入orcale数据到hdfs
https://ask.hellobi.com/blog/hql15/546
操作如下:
drop table if exists vio_fact_violation;
create external table if not exists vio_fact_violation(WFRQBM string,XZQHBM string,GLBMBM string,WFSDBM string,WFXQBM string,FXJGBM string,CLJGBM string,ZQMJBM string,CLSJBM string,JSRSJBM string,HPZLBM string,HPHM string, JTFSBM string,FXLDBM string, WFXWBM string, WFCLBM string, CFZLBM string, JDSLBBM string, QZCSLXBM string, SJLYBM string, JKFSBM string, JKBJBM string, RYFLBM string, JLLXBM VARCHAR2(1), WFS NUMBER(6), CLS NUMBER(6), XCCLS NUMBER(6), FXCCLS NUMBER(6), QZCSCLS NUMBER(6), JKS NUMBER(6), WJKS NUMBER(6), JFS NUMBER(6), FKJE NUMBER(10,2), CLDXBM string, WCLS int, WFRQ DATE, FZJGBM string, SJYB string, WFLXBM string, QZCSFXS int, WFCLSJBM string, FXDLBM string, ZQMJBM2 string, WFBH string, SYXZBM string, FXLDBM2 string, XZQHBM2 string, FXJGBM2 string, CLJGBM2 string, QZCSPZBH string) row format delimited fields terminated by ',' location '/vio_project/vio_fact_violation';
drop table if exists vio_dim_xzqh_new;
create external table if not exists vio_dim_xzqh_new(xzqhbm string,xzqhmc string,SHENGBM string,SHENGMC string,SHIBM string,SHIMC string) row format delimited fields terminated by ',' location '/vio_project/VIO_DIM_XZQH_NEW';
and so on
6、对BI工具新建数据源指定到步骤5生成的对应仓库的对应表->数据模型源选择正确->可视化展现
参考:
Cognos连接Hive配置:
https://ask.hellobi.com/blog/hql15/3572
Mstr连接Hive配置:
https://ask.hellobi.com/blog/hql15/3449