前沿,因公司数据量较大,cognos访问oracle数据库,报表展现速度很蜗牛,所以觉得用Hive做仓库,使用TDH的Inceptor内存技术,加快报表查询性能<_/\_>
Cognos对Hive版本要求很严格:
Cognos BI 10.2.1 supports Hive versions 0.8, 0.9, 0.11 and 0.12 as of Fix Pack 3.
Hive 0.8 and 0.9 are supported through the original Hive connectivity interface, therefore the following default 10.2.1 JDBC URL and Driver class name formats should be used:
JDBC URL: jdbc:hive://<host>:<port>/<database-name>
Driver class name: org.apache.hadoop.hive.jdbc.HiveDriver
Hive 0.11 and 0.12 are supported through Hive2 connectivity, therefore the default formats for the JDBC URL and Driver class name fields need to be manually edited to the following:
JDBC URL: jdbc:hive2://<host>:<port>/<database-name>
Driver class name: org.apache.hive.jdbc.HiveDriver
The Hive server client must align with the HiveServer run at the server. In other words, in the event that a Hive 0.12 or 0.11 environment has chosen to use HiveServer and not HiveServer2 the correct class and URI must be used.
Future versions of Cognos BI will default to the Hive2 JDBC URL
刚开始使用Cognos10.2.1连接Hiveserver1,坑爹,需要打补丁:
IBM Cognos 10.2.1补丁编号:
10.2.5003.113 - 10.2.1 FP3
1)解压补丁包
2)cd /root/App/Cognos/fixnuxi38664h
./issetup
3)其余安装过程类似,注意目录同cognos安装目录
4)版本查看
/opt/ibm/cognos/c10_64/cmplst.txt中会有如下内容:
C8BISRVR_UPDATE_version=C8BISRVR-LX64-ML-RTM-10.2.5003.113-0(补丁版本号)
下面使用Cognos10.2.2安装,10.2.2支持Hiveserver2,具体流程如下:
1、安装Hiveserver2,登录集群控制台点击添加服务->选择Inceptor-Sql->选择依赖的服务(本实例选择依赖Hyperbase,因后续项目会同时使用)->为服务分配角色(要和Hiveserver1节点区分)->简单认证模式->高级参数hive.server2.enabled选择为True->Go to end;
2、安装完成Hiveserver2,进入Inceptor控制节点,允许:beeline -u jdbc:hive2://192.178.20.31:10000/default,ok 成功!
3、在Hiveserver2节点运行getInceptorDriver.sh,获取Inceptor节点jar包;
4、把jar包导入到cognos安装目录/opt/ibm/cognos/c10_64/webapps/p2pd/WEB-INF/lib 和 /opt/ibm/cognos/c10_64/v5dataserver/lib/下;
5、拷贝目标集群的/etc/inceptorsql1/conf/hive-site.xml到/opt/ibm/cognos/c10_64/webapps/p2pd/WEB-INF/classes/;
6、重启Cognos;
7、登录cognos的Ibm cognos connection,新建数据源连接;
8、指定此新建数据源的连接参数类型选择jdbc;
9、编辑参数以构建 JDBC 连接字符串:类型选择hive,JDBC URL选择jdbc:hive2://<host>:<port>/<database-name>,选择无身份验证,点击测试,OK,成功。
10、登录FM,运行元数据向导,选择配置好的Hive数据源,导入Hive仓库数据表。
附:日志位置:/opt/ibm/cognos/c10_64/logs,测试连接的日志主要在XQE目录下,日志信息很详细,基本能够trace整个过程。
getInceptorDriver:
#!/bin/sh
dir=inceptor-driver
rm -rf $dir
mkdir $dir
cd $dir
mkdir "tmp"
array=(
"commons-cli-*.jar"
"commons-codec-*.jar"
"commons-collections-*.jar"
"commons-configuration-*.jar"
"commons-lang-*.jar"
"commons-logging-*.jar"
"guava-*.jar"
"hadoop-annotations-*-transwarp.jar"
"hadoop-auth-*-transwarp.jar"
"hadoop-common-*-transwarp.jar"
"hadoop-hdfs-*-transwarp.jar"
"hive-common-*-transwarp-tdh*.jar"
"hive-jdbc-*-transwarp-tdh*.jar"
"hive-metastore-*-transwarp-tdh*.jar"
"hive-serde-*-transwarp-tdh*.jar"
"hive-service-*-transwarp-tdh*.jar"
"httpclient-*.jar"
"httpcore-*.jar"
"libfb303-*jar"
"libthrift-*jar"
"log4j-*.jar"
"slf4j-api-*.jar"
"slf4j-log4j12-*.jar"
)
#for shims
cp /usr/lib/hive/lib/hive-shims-*-transwarp-tdh*.jar ./tmp/
for item in ${array[@]}
do
# echo "----------------------------------------------------------------------------------"
files=`find /usr/lib/hive/ /usr/lib/hadoop/ /usr/lib/hadoop-hdfs/ -name "$item"`
# echo $files
maxFile=""
maxValue=0
for file in ${files[@]}
do
# echo $file
segments=${file//// }
name=""
for segment in ${segments[@]}
do
name=$segment
done
# echo $name
nums=`echo "$name" |grep -o "[0-9]*"`
value=0
for num in ${nums[@]}
do
value=$[value*100+num];
done
# echo $value
if [ $maxValue -le $value ];
then
maxValue=$value
maxFile=$file
fi
done
# echo "---------------- $maxFile --------------------"
cmd="cp -f $maxFile ./tmp/"
# echo $cmd
$cmd
# echo "----------------------------------------------------------------------------------"
# echo ""
# echo ""
done
#-----------------------------------------------
# Check all the jars
#-----------------------------------------------
cd "./tmp/"
success=1
for item in ${array[@]}
do
# echo $item
files=`find -name "$item"`
if [ -z "$files" ];
then
echo " File not found: --> $item"
success=0
fi
done
if [ $success -eq 0 ];
then
echo ""
echo " -------------------------------------------------"
echo " | PLEASE MAKE SURE ALL THE FILES IS FOUND! |"
echo " -------------------------------------------------"
exit 1;
fi
result=`find -name \*.jar -exec jar -xf {} \;`
result=`find -name hive-jdbc-\*.jar -exec jar -xf {} \;`
branch=`grep BuildScmBranch META-INF/MANIFEST.MF -i`
version=`echo "$branch" |grep -o "[0-9]\+\.[0-9]\+\(\.[0-9]\+\)*"`
#echo $version
targetJar=$dir"-"$version".jar"
targetTar=$dir"-"$version".tar"
targetFiles=$dir"-"$version"-files"
if [ -z $version ];
then
targetJar=$dir".jar"
targetTar=$dir".tar"
targetFiles=$dir"-files"
fi
result=`tar cvf ../$targetTar *.jar`
result=`mkdir -p ../$targetFiles/`
result=`find -name \*.jar |sort |tee filelist `
result=`mv ./*.jar ../$targetFiles/`
result=`jar cfM ../$targetJar ./`
cd ..
#result=`rm -rf "./tmp/"`
path=`pwd`
echo " All-in-one Driver:"
echo " $path/$targetJar"
echo ""
echo " Separated Driver files:"
echo " $path/$targetTar"
echo ""
cd ..
#rm -rf $dir