Hive的基本数据类型与Java类似Hive的数据类型Java是数据类型长度TINYINTbyte1byte整数SMALINTshort2byte整数INTint4byte整数BIGINTlong8byte整数BOOLEANboolean布尔值FLOATfloat单精度浮点数DOUBLEdouble双精度浮点数STRINGstring字符串TIMESTAMP时间类型BINARY字节数组Hive的集合数据类型数据类型描述语法实例实例ARRAY相当于Java的数组array<基本类型>[‘Bob’,’bigdataboy’,’cn’]MAP相当与Java的Array集合，键值对map<基本类型-键,基本类型-值>{‘name’,’bigdataboy’}STRUCT是一个复合结构类型struct<>{‘province’:基本类型,’city’:基本类型}集合数据类型测试字段及类型四个字段namestring,friendsarray<string>,childrenmap<string,int>,addrstruct<'省':string,'市':string>分割符说明字段使用,(英文逗号)分隔array<>类型使用_(下划线)分割map<>类型手机用:(冒号)分割测试数据#数据说明Bob,第一个字段aa_bb,第二个array<>字段aa:12_bb:13,第三个map<>字段四川_成都第四个struct<>字段#完整测试数据Bob,aa_bb,aa:12_bb:13,四川_成都Black,cc_dd,cc:24_dd:23,四川_泸州Hive创建表createtableinfor(namestring,friendsarray<string>,childrenmap<string,int>,addrstruct<province:string,city:string>)rowformatdelimitedfieldsterminatedby','collectionitemsterminatedby'_'mapkeysterminatedby':'linesterminatedby'\n';在Hive中创建好表，加载到Hive中loaddatalocalinpath'加载的测试数据文件路径'intotableinfor;Hive查看加载的数据#查看所有数据hive>select*frominfor;OKBob["aa","bb"]{"aa":12,"bb":13}{"province":"四川","city":"成都"}Black["cc","dd"]{"cc":24,"dd":23}{"province":"四川","city":"泸州"}Timetaken:2.4seconds,Fetched:2row(s)#查看不同类型的数据hive>selectname,friends[0],children['aa'],addr.provincefrominfor;OKBobaa12四川BlackccNULL四川Timetaken:0.098seconds,Fetched:2row(s)
以下命令针对的是Hive1.2.2所有的shell命令[root@master~]#hive-helpusage:hive-d,--define<key=value>Variablesubsitutiontoapplytohivecommands.e.g.-dA=Bor--defineA=B--database<databasename>Specifythedatabasetouse-e<quoted-query-string>SQLfromcommandline-f<filename>SQLfromfiles-H,--helpPrinthelpinformation--hiveconf<property=value>Usevalueforgivenproperty--hivevar<key=value>Variablesubsitutiontoapplytohivecommands.e.g.--hivevarA=B-i<filename>InitializationSQLfile-S,--silentSilentmodeininteractiveshell-v,--verboseVerbosemode(echoexecutedSQLtotheconsole)常用命令1-e无需打开Hive执行HQL命令[root@master~]#hive-e'select*fromstudent'Logginginitializedusingconfigurationinjar:file:/usr/local/src/hive/apache-hive-1.2.2-bin/lib/hive-common-1.2.2.jar!/hive-log4j.propertiesOK1aa2bb3ccTimetaken:12.088seconds,Fetched:3row(s)常用命令2-f无需打开Hive执行文件中的HQL命令\>把查询结果导出到文件[root@master~]#hive-fstu.hql>stu_res.txtLogginginitializedusingconfigurationinjar:file:/usr/local/src/hive/apache-hive-1.2.2-bin/lib/hive-common-1.2.2.jar!/hive-log4j.propertiesOKTimetaken:12.065seconds,Fetched:3row(s)[root@master~]#ll总用量16-rw-------.1rootroot134011月422:29anaconda-ks.cfg-rw-r--r--.1rootroot2312月2817:22stu.hql-rw-r--r--.1rootroot1512月2817:25stu_res.txt[root@master~]#catstu_res.txt1aa2bb3ccHive常用的交互命令退出命令hive>exit;先隐性提交数据，再退出hive>quit;不提交数据，退出打开HDFS目录dfs-ls/hive>dfs-ls/>;Found2itemsdrwx-wx-wx-rootsupergroup02019-12-2616:46/tmpdrwxr-xr-x-rootsupergroup02019-12-2816:13/user打开本地目录!ls/roothive>!ls/root;anaconda-ks.cfgstudent.txtstu.hqlstu_res.txt查看Hive中输入的所有历史命令在当前用户的根目录查看cat.hivehistory
1、问题描述[ERROR]Terminalinitializationfailed;fallingbacktounsupported处理删除$HADOOP_HOME/share/hadoop/yarn/lib/jline-0.9.94.jar文件然后重新启动hive即可2、问题描述[root@masterconf]#schematool-dbTypemysql-initSchemaMetastoreconnectionURL:jdbc:mysql://192.168.176.65:3306/hiveMetastoreConnectionDriver:com.mysql.jdbc.DriverMetastoreconnectionUser:rootorg.apache.hadoop.hive.metastore.HiveMetaException:Failedtogetschemaversion.***schemaToolfailed***[root@masterconf]#cd..处理开启MySQL的远程访问，并重启MySQL
相关版本名称详情Hive1.2.2（下载地址）MySQL驱动connector-java-5.1.48（下载地址）MySQL安装教程地址解压Hive的tar包#进入src目录cd/usr/local/src/#创建hive目录mkdirhive#解压tar包到创建的hive目录tar-zxvfapache-hive-1.2.2-bin.tar.gz-Chive/配置环境变量#编辑环境变量vi~/.bash_profile#加入环境变量#HiveexportHIVE_HOME=/usr/local/src/hive/apache-hive-1.2.2-binexportPATH=$HIVE_HOME/bin:$PATH:#更新环境变量source~/.bash_profile修改Hive配置文件#进入conf[root@masterconf]#ll总用量188-rw-rw-r--.1rootroot11394月302015beeline-log4j.properties.template-rw-rw-r--.1rootroot1684316月192015hive-default.xml.template-rw-rw-r--.1rootroot23784月302015hive-env.sh.template-rw-rw-r--.1rootroot26624月302015hive-exec-log4j.properties.template-rw-rw-r--.1rootroot30504月302015hive-log4j.properties.template-rw-rw-r--.1rootroot15934月302015ivysettings.xml#复制hive-env.sh模板[root@masterconf]#cphive-env.sh.templatehive-env.sh#编辑hive-env.sh的内容[root@masterconf]#vihive-env.sh#加入HADOOP_HOME的路径#SetHADOOP_HOMEtopointtoaspecifichadoopinstalldirectoryHADOOP_HOME=/usr/local/src/hadoop/hadoop-2.9.2#修改了HIVE的配置，指定修改的配置的文件#HiveConfigurationDirectorycanbecontrolledby:exportHIVE_CONF_DIR=/usr/local/src/hive/apache-hive-1.2.2-bin/conf#保存退出，更新hive-env.sh文件[root@masterconf]#sourcehive-env.sh尝试进入Hive，在退出在进入之前需要删除一个文件删除$HADOOP_HOME/share/hadoop/yarn/lib/jline-0.9.94.jar文件[root@masterconf]#hiveLogginginitializedusingconfigurationinjar:file:/usr/local/src/hive/apache-hive-1.2.2-bin/lib/hive-common-1.2.2.jar!/hive-log4j.propertieshive>exit;[root@masterconf]#MySQL配置首先进入MySQL里，修改完成后，记得重启数据库1、开启MySQL远程连接mysql>GRANTALLPRIVILEGESON*.*TO'root'@'%'IDENTIFIEDBY'密码';mysql>flushprivileges;2、创建一个数据库mysql>createdatabasehive;QueryOK,1rowaffected(0.00sec)配置Hive数据库为MySQL需要在$HIVE_HOME的conf目录下复制一个模板如果不复制模板，可以单独创建hive-site.xml文件，注意使用<configuration>标签把配置包裹起来cphive-default.xml.templatehive-site.xml<!--配置MySQL数据库地址--><property><name>javax.jdo.option.ConnectionURL</name><value>jdbc:mysql://MySQL的IP:3306/hive?createDatabaseIfNotExist=true&useSSL=false</value><description>JDBCconnectstringforaJDBCmetastore</description></property><!--配置MySQL驱动--><property><name>javax.jdo.option.ConnectionDriverName</name><value>com.mysql.jdbc.Driver</value><description>DriverclassnameforaJDBCmetastore</description></property><!--配置用户名--><property><name>javax.jdo.option.ConnectionUserName</name><value>root</value><description>Usernametouseagainstmetastoredatabase</description></property><!--配置密码--><property><name>javax.jdo.option.ConnectionPassword</name><value>Aa@12345678</value><description>passwordtouseagainstmetastoredatabase</description></property><!--复制模板的需要添加--><property><name>system:java.io.tmpdir</name><value>/usr/local/src/hive/tmpdir</value></property><property><name>system:user.name</name><value>hive</value></property>初始化Hive数据库首先需要把MySQL驱动放入Hive的lib目录下[root@masterconf]#schematool-dbTypemysql-initSchemaMetastoreconnectionURL:jdbc:mysql://192.168.176.65:3306/hiveMetastoreConnectionDriver:com.mysql.jdbc.DriverMetastoreconnectionUser:rootStartingmetastoreschemainitializationto1.2.0Initializationscripthive-schema-1.2.0.mysql.sqlInitializationscriptcompletedschemaToolcompleted（初始化完成）[root@masterconf]#启动Hive[root@masterhive]#hiveLogginginitializedusingconfigurationinjar:file:/usr/local/src/hive/apache-hive-1.2.2-bin/lib/hive-common-1.2.2.jar!/hive-log4j.propertieshive>推荐阅读：Hive搭建常见错误