{"id":1462,"date":"2022-06-19T00:24:17","date_gmt":"2022-06-18T16:24:17","guid":{"rendered":"http:\/\/www.eait.co\/?p=1462"},"modified":"2022-06-19T00:24:17","modified_gmt":"2022-06-18T16:24:17","slug":"spark-sql%e8%bf%9e%e6%8e%a5hive","status":"publish","type":"post","link":"https:\/\/notes.coremix.net\/?p=1462","title":{"rendered":"spark-SQL\u8fde\u63a5Hive"},"content":{"rendered":"

server\u7aef\uff08biddata166\u4e3b\u673ahive\/conf\u76ee\u5f55\u4e0bhive-site.xml\uff09\uff1a<\/p>\n

\r\n<configuration> \r\n  <property> \r\n    <name>hive.metastore.warehouse.dir<\/name>  \r\n    <value>\/user\/hive\/warehouse<\/value> \r\n  <\/property>  \r\n  <property> \r\n    <name>javax.jdo.option.ConnectionURL<\/name>  \r\n    <value>jdbc:mysql:\/\/bigdata166:3306\/metastore?serverTimezone=UTC<\/value> \r\n  <\/property>  \r\n  <property> \r\n    <name>javax.jdo.option.ConnectionDriverName<\/name>  \r\n    <value>com.mysql.jdbc.Driver<\/value> \r\n  <\/property>  \r\n  <property> \r\n    <name>javax.jdo.option.ConnectionUserName<\/name>  \r\n    <value>root<\/value> \r\n  <\/property>  \r\n  <property> \r\n    <name>javax.jdo.option.ConnectionPassword<\/name>  \r\n    <value>000000<\/value> \r\n  <\/property>  \r\n  <property> \r\n    <name>hive.querylog.location<\/name>  \r\n    <value>\/user\/hive\/iotmp<\/value> \r\n  <\/property>  \r\n  <property> \r\n    <name>hive.server2.logging.operation.log.location<\/name>  \r\n    <value>\/user\/hive\/operation_logs<\/value> \r\n  <\/property>  \r\n  <property> \r\n    <name>datanucleus.readOnlyDatastore<\/name>  \r\n    <value>false<\/value> \r\n  <\/property>  \r\n  <property> \r\n    <name>datanucleus.fixedDatastore<\/name>  \r\n    <value>false<\/value> \r\n  <\/property>  \r\n  <property> \r\n    <name>datanucleus.autoCreateSchema<\/name>  \r\n    <value>true<\/value> \r\n  <\/property>  \r\n  <property> \r\n    <name>datanucleus.autoCreateTables<\/name>  \r\n    <value>true<\/value> \r\n  <\/property>  \r\n  <property> \r\n    <name>datanucleus.autoCreateColumns<\/name>  \r\n    <value>true<\/value> \r\n  <\/property> \r\n<property>\r\n    <name>datanucleus.schema.autoCreateAll<\/name>\r\n    <value>true<\/value>\r\n  <\/property>\r\n<\/configuration>\r\n<\/pre>\n
 <\/p>\n
\u542f\u52a8\u670d\u52a1\u7aef\uff1ahive –service metastore \u7b49\u5f85<\/span><\/span><\/p>\n
\u60ac\u505c\u5e76\u5360\u7528\u4f1a\u8bdd\uff0c\u53ef\u7528&<\/span><\/span><\/p>\n
client\u7aef(hive-site.xml \u540c\u65f6\u9700\u8981\u5c06\u4e3b\u673ahive\u76ee\u5f55\u590d\u5236\u5230\u5ba2\u6237\u673a)<\/p>\n
\r\n<configuration> \r\n  <property> \r\n    <name>hive.metastore.warehouse.dir<\/name>  \r\n    <value>\/user\/hive\/warehouse<\/value> \r\n  <\/property>  \r\n  <property> \r\n    <name>hive.metastore.local<\/name>  \r\n    <value>false<\/value> \r\n  <\/property>  \r\n  <property> \r\n    <name>hive.metastore.uris<\/name>  \r\n    <value>thrift:\/\/bigdata166:9083<\/value> \r\n  <\/property> \r\n<\/configuration>\r\n<\/pre>\n
\u542f\u52a8\u5ba2\u6237\u7aef\uff1ahive(\u4ec5\u4f5c\u6d4b\u8bd5\u548c\u67e5\u770bspark\u548chive\u4e92\u901a\u7ed3\u679c)<\/span><\/span><\/p>\n
\u7136\u540e\u5c06\u5ba2\u6237\u7aef\u914d\u7f6e\u6587\u4ef6\u53d1\u9001\u5230\u4e3b\u673aspark\u7684conf\u4e2d<\/span><\/span><\/p>\n
\u7136\u540e\u5c06core\u548chdfs-site\u4e24\u4e2a\u6587\u4ef6\u590d\u5236\u5230\u4e3b\u673aspark\/conf\u4e2d<\/span><\/span><\/p>\n
cp \/usr\/local\/hadoop-2.8.4\/etc\/hadoop\/core-site.xml .\/conf\/
\ncp \/usr\/local\/hadoop-2.8.4\/etc\/hadoop\/hdfs-site.xml .\/conf\/<\/p>\n
\u4e3b\u8282\u70b9\u4e0a\u542f\u52a8spark start-all.sh<\/span><\/span><\/p>\n
shell\u4e00\u5b9a\u8981\u542f\u52a8\u96c6\u7fa4\u6a21\u5f0f\u7684(\u5982\u679c\u4e0d\u7528\u8fde\u63a5hive\uff0c\u5907\u6ce8\u6389\u4e09\u4e2a\u6587\u4ef6\u5373\u53ef mv xxx .xxx-bak)<\/span><\/span><\/p>\n
\u5176\u5b83\u64cd\u4f5c\uff1a<\/strong><\/p>\n
\r\n.\/bin\/spark-shell --master spark:\/\/bigdata166:7077\r\n\r\nspark.sql("show tables").show\r\n\t\r\nspark.sql("select * from company.emp limit 10").show\r\nspark.sql("select * from student1 limit 10").show  \/\/hive\u4e2d\u7684\u8868\r\n\r\nspark.sql("create table company.emp_1104(empno Int,ename String,job String,mgr String,hiredate String,sal Int,comm String,deptno Int) row format delimited fields terminated by ','");\r\n\r\nspark.sql("load data local inpath '\/testdata\/emp.csv' overwrite into table company.emp_1104");\r\n\r\nspark.sql("select * from company.emp_1104 limit 10").show\r\n\r\n\r\n\r\nscala> spark.sql("show tables").show\r\n+--------+---------+-----------+\r\n|database|tableName|isTemporary|\r\n+--------+---------+-----------+\r\n| default|      emp|      false|\r\n| default| emp_1120|      false|\r\n+--------+---------+-----------+\r\n\r\n\r\nscala> spark.sql("create table default.student (id Int,name String,age Int) row format delimited fields terminated by "\\t"").show\r\n<console>:24: error: value \\ is not a member of String\r\n       spark.sql("create table default.student (id Int,name String,age Int) row format delimited fields terminated by "\\t"").show\r\n                                                                                                                       ^\r\n<console>:24: error: value t is not a member of StringContext\r\n       spark.sql("create table default.student (id Int,name String,age Int) row format delimited fields terminated by "\\t"").show\r\n                                                                                                                        ^\r\n\r\nscala> spark.sql("create table default.student (id Int,name String,age Int) row format delimited fields terminated by '\\t'").show\r\n++\r\n||\r\n++\r\n++\r\n\r\n\r\nscala> spark.sql("show tables").show\r\n+--------+---------+-----------+\r\n|database|tableName|isTemporary|\r\n+--------+---------+-----------+\r\n| default|      emp|      false|\r\n| default| emp_1120|      false|\r\n| default|  student|      false|\r\n+--------+---------+-----------+\r\n\r\n\r\nscala> spark.sql("load data local inpath '\/opt\/module\/test\/student.txt' into table default.student").show\r\n20\/01\/07 06:08:10 ERROR KeyProviderCache: Could not find uri with key [dfs.encryption.key.provider.uri] to create a keyProvider !!\r\n++\r\n||\r\n++\r\n++\r\n\r\n\r\nscala> spark.sql("show tables").show\r\n+--------+---------+-----------+\r\n|database|tableName|isTemporary|\r\n+--------+---------+-----------+\r\n| default|      emp|      false|\r\n| default| emp_1120|      false|\r\n| default|  student|      false|\r\n+--------+---------+-----------+\r\n\r\n\r\nscala> spark.sql("select * from student").show\r\n+---+----+----+\r\n| id|name| age|\r\n+---+----+----+\r\n|  1|  \u5415\u5e03|3500|\r\n|  2|  \u8c82\u8749|3480|\r\n|  3|  \u8463\u5353|5000|\r\n|  4|  \u8d75\u4e91|2500|\r\n|  5|  \u5f20\u98de|2500|\r\n+---+----+----+\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
server\u7aef\uff08biddata166\u4e3b\u673ahive\/conf\u76ee\u5f55\u4e0bhive-site.xml\uff09\uff1a <con […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1462","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/notes.coremix.net\/index.php?rest_route=\/wp\/v2\/posts\/1462","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/notes.coremix.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/notes.coremix.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/notes.coremix.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/notes.coremix.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1462"}],"version-history":[{"count":1,"href":"https:\/\/notes.coremix.net\/index.php?rest_route=\/wp\/v2\/posts\/1462\/revisions"}],"predecessor-version":[{"id":1464,"href":"https:\/\/notes.coremix.net\/index.php?rest_route=\/wp\/v2\/posts\/1462\/revisions\/1464"}],"wp:attachment":[{"href":"https:\/\/notes.coremix.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1462"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/notes.coremix.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1462"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/notes.coremix.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1462"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}