SparkSQL 数据源
3.SparkSQL 数据源
3.1 通用加载/保存方法
3.1.1 手动指定选项
Spark SQL 的 DataFrame 接口支持多种数据源的操作。一个 DataFrame 可以进行 RDDs 方式
的操作,也可以被注册为临时表。把 DataFrame 注册为临时表之后,就可以对该 DataFrame 执行
SQL 查询。
Spark SQL 的默认数据源为 Parquet 格式。数据源为 Parquet 文件时,Spark SQL 可以方便的
执行所有的操作。修改配置项 spark.sql.sources.default,可修改默认数据源格式。
val df = spark.read.load("examples/src/main/resources/users.parquet") df.select("name", "favorite_color").write.save("namesAndFavColors.parquet")
当数据源格式不是 parquet 格式文件时,需要手动指定数据源的格式。数据源格式需要指定
全名(例如:org.apache.spark.sql.parquet),如果数据源格式为内置格式,则只需要指定简称定
json, parquet, jdbc, orc, libsvm, csv, text 来指定数据的格式。
可以通过 SparkSession 提供的 read.load 方法用于通用加载数据,使用 write 和 save 保存数
据。
val peopleDF = spark.read.format("json").load("examples/src/main/resources/people.json") peopleDF.write.format("parquet").save("hdfs://hadoop102:9000/namesAndAges.parquet")
除此之外,可以直接运行 SQL 在文件上:
val sqlDF = spark.sql("SELECT * FROM parquet.`hdfs://hadoop102:9000/namesAndAges.parquet`") sqlDF.show()
scala> val peopleDF = spark.read.format("json").load("examples/src/main/resources/people.json") peopleDF: org.apache.spark.sql.DataFrame = [age: bigint, name: string]
scala> peopleDF.write.format("parquet").save("hdfs://hadoop102:9000/namesAndAges.parquet")
scala> peopleDF.show() +----+-------+ | age| name| +----+-------+ |null|Michael| | 30| Andy| | 19| Justin| +----+-------+
scala> val sqlDF = spark.sql("SELECT * FROM parquet.`hdfs://hadoop102:9000/namesAndAges.parquet`") sqlDF: org.apache.spark.sql.DataFrame = [age: bigint, name: string] scala> sqlDF.show() +----+-------+ | age| name| +----+-------+ |null|Michael| | 30| Andy| | 19| Justin| +----+-------+
3.1.2 文件保存选项
可以采用 SaveMode 执行存储操作,SaveMode 定义了对数据的处理模式。需要注意的是,这
些保存模式不使用任何锁定,不是原子操作。此外,当使用 Overwrite 方式执行时,在输出新数
据之前原数据就已经被删除。SaveMode 详细介绍如下表:
![](https://img2018.cnblogs.com/blog/1604514/201907/1604514-20190709012003247-617878900.png)
3.2 JSON 文件
Spark SQL 能够自动推测 JSON 数据集的结构,并将它加载为一个 Dataset[Row]. 可以通过
SparkSession.read.json()去加载一个 一个 JSON 文件。
注意:这个 JSON 文件不是一个传统的 JSON 文件,每一行都得是一个 JSON 串。
{"name":"Michael"} {"name":"Andy", "age":30} {"name":"Justin", "age":19} // Primitive types (Int, String, etc) and Product types (case classes) encoders are // supported by importing this when creating a Dataset. import spark.implicits._ // A JSON dataset is pointed to by path. // The path can be either a single text file or a directory storing text files val path = "examples/src/main/resources/people.json" val peopleDF = spark.read.json(path)
// The inferred schema can be visualized using the printSchema() method peopleDF.printSchema() // root // |-- age: long (nullable = true) // |-- name: string (nullable = true) // Creates a temporary view using the DataFrame peopleDF.createOrReplaceTempView("people")
// SQL statements can be run by using the sql methods provided by spark val teenagerNamesDF = spark.sql("SELECT name FROM people WHERE age BETWEEN 13 AND 19") teenagerNamesDF.show() // +------+ // | name| // +------+ // |Justin| // +------+
// Alternatively, a DataFrame can be created for a JSON dataset represented by
// a Dataset[String] storing one JSON object per string val otherPeopleDataset = spark.createDataset("""{"name":"Yin","address":{"city":"Columbus","state":"Ohio"}}""" :: Nil) val otherPeople = spark.read.json(otherPeopleDataset)
otherPeople.show() // +---------------+----+ // | address|name| // +---------------+----+ // |[Columbus,Ohio]| Yin|
3.3 Parquet 文件
Parquet 是一种流行的列式存储格式,可以高效地存储具有嵌套字段的记录。Parquet 格式经
常在 Hadoop 生态圈中被使用,它也支持 Spark SQL 的全部数据类型。Spark SQL 提供了直接读
取和存储 Parquet 格式文件的方法。
importing spark.implicits._ import spark.implicits._ val peopleDF = spark.read.json("examples/src/main/resources/people.json") peopleDF.write.parquet("hdfs://hadoop102:9000/people.parquet") val parquetFileDF = spark.read.parquet("hdfs://hadoop102:9000/people.parquet") parquetFileDF.createOrReplaceTempView("parquetFile") val namesDF = spark.sql("SELECT name FROM parquetFile WHERE age BETWEEN 13 AND 19") namesDF.map(attributes => "Name: " + attributes(0)).show() // +------------+ // | value| // +------------+ // |Name: Justin| // +------------+
3.4 JDBC
Spark SQL 可以通过 JDBC 从关系型数据库中读取数据的方式创建 DataFrame,通过对
DataFrame 一系列的计算后,还可以将数据再写回关系型数据库中。
注意,需要将相关的数据库驱动放到 spark 的类路径下。
$ bin/spark-shell --master spark://hadoop102:7077 --jars mysqlconnector-java-5.1.27-bin.jar // Note: JDBC loading and saving can be achieved via either the load/save or jdbc methods
// Loading data from a JDBC source val jdbcDF = spark.read.format("jdbc").option("url", "jdbc:mysql://hadoop102:3306/rdd").option("dbtable", "rddtable").option("user", "root").option("password", "hive").load() val connectionProperties = new Properties() connectionProperties.put("user", "root") connectionProperties.put("password", "hive") val jdbcDF2 = spark.read.jdbc("jdbc:mysql://hadoop102:3306/rdd", "rddtable", connectionProperties)
// Saving data to a JDBC source jdbcDF.write.format("jdbc").option("url", "jdbc:mysql://hadoop102:3306/rdd").option("dbtable", "rddtable2").option("user", "root").option("password", "hive").save() jdbcDF2.write.jdbc("jdbc:mysql://hadoop102:3306/mysql", "db", connectionProperties)
// Specifying create table column data types on write jdbcDF.write.option("createTableColumnTypes", "name CHAR(64), comments VARCHAR(1024)").jdbc("jdbc:mysql://hadoop102:3306/mysql", "db", connectionProperties)
3.5 Hive 数据库
Apache Hive 是 Hadoop 上的 SQL 引擎,Spark SQL 编译时可以包含 Hive 支持,也可以不包
含。包含 Hive 支持的 Spark SQL 可以支持 Hive 表访问、UDF(用户自定义函数)以及 Hive 查询
语言(HiveQL/HQL)等。需要强调的一点是,如果要在 Spark SQL 中包含 Hive 的库,并不需要事
先安装 Hive。一般来说,最好还是在编译 Spark SQL 时引入 Hive 支持,这样就可以使用这些特
性了。如果你下载的是二进制版本的 Spark,它应该已经在编译时添加了 Hive 支持。
若要把 Spark SQL 连接到一个部署好的 Hive 上,你必须把 hive-site.xml 复制到 Spark 的配
置文件目录中($SPARK_HOME/conf)。即使没有部署好 Hive,Spark SQL 也可以运行。 需要注意
的是,如果你没有部署好 Hive,Spark SQL 会在当前的工作目录中创建出自己的 Hive 元数据仓
库,叫作 metastore_db。此外,如果你尝试使用 HiveQL 中的 CREATE TABLE (并非 CREATE
EXTERNAL TABLE)语句来创建表,这些表会被放在你默认的文件系统中的 /user/hive/warehouse
目录中(如果你的 classpath 中有配好的 hdfs-site.xml,默认的文件系统就是 HDFS,否则就是本
地文件系统)。
import java.io.File import org.apache.spark.sql.Row import org.apache.spark.sql.SparkSession
case class Record(key: Int, value: String) // warehouseLocation points to the default location for managed databases and tables val warehouseLocation = new File("spark-warehouse").getAbsolutePath val spark = SparkSession.builder().appName("Spark Hive Example").config("spark.sql.warehouse.dir", warehouseLocation).enableHiveSupport().getOrCreate()
import spark.implicits._ import spark.sql sql("CREATE TABLE IF NOT EXISTS src (key INT, value STRING)") sql("LOAD DATA LOCAL INPATH 'examples/src/main/resources/kv1.txt' INTO TABLE src")
// Queries are expressed in HiveQL sql("SELECT * FROM src").show()
// +---+-------+ // |key| value| // +---+-------+ // |238|val_238| // | 86| val_86| // |311|val_311| // ...
// Aggregation queries are also supported. sql("SELECT COUNT(*) FROM src").show() // +--------+ // |count(1)| // +--------+ // | 500 | // +--------+ // The results of SQL queries are themselves DataFrames and support all normal functions. val sqlDF = sql("SELECT key, value FROM src WHERE key < 10 ORDER BY key")
// The items in DataFrames are of type Row, which allows you to access each column by ordinal. val stringsDS = sqlDF.map { case Row(key: Int, value: String) => s"Key: $key, Value: $value" } stringsDS.show() // +--------------------+ // | value| // +--------------------+ // |Key: 0, Value: val_0| // |Key: 0, Value: val_0| // |Key: 0, Value: val_0| // ... // You can also use DataFrames to create temporary views within a SparkSession. val recordsDF = spark.createDataFrame((1 to 100).map(i => Record(i, s"val_$i"))) recordsDF.createOrReplaceTempView("records")
// Queries can then join DataFrame data with data stored in Hive. sql("SELECT * FROM records r JOIN src s ON r.key = s.key").show() // +---+------+---+------+ // |key| value|key| value| // +---+------+---+------+ // | 2| val_2| 2| val_2| // | 4| val_4| 4| val_4| // | 5| val_5| 5| val_5|
3.5.1 内嵌 Hive 应用
如果要使用内嵌的 Hive,什么都不用做,直接用就可以了。 --conf :
spark.sql.warehouse.dir=
注意:如果你使用的是内部的 Hive,在 Spark2.0 之后,spark.sql.warehouse.dir
用于指定数据仓库的地址,如果你需要是用 HDFS 作为路径,那么需要将 core
site.xml 和 hdfs-site.xml 加入到 Spark conf 目录,否则只会创建 master 节点上的
笔记:
使用内置hive:
/*
local模式下使用内置 hive
*/
scala> import spark.implicits._ import spark.implicits._ scala> spark.sql("create table kv(key Int,value String)") 19/07/09 07:36:54 WARN HiveMetaStore: Location: file:/opt/module/spark/spark-warehouse/kv specified for non-external table:kv res0: org.apache.spark.sql.DataFrame = [] scala> spark.sql("load data local inpath 'examples/src/main/resources/kv1.txt' into table kv") res1: org.apache.spark.sql.DataFrame = [] scala> spark.sql("select * from kv").show +---+-------+ |key| value| +---+-------+ |238|val_238| | 86| val_86| |311|val_311| | 27| val_27| |165|val_165| |409|val_409| |255|val_255| |278|val_278| | 98| val_98| |484|val_484| |265|val_265| |193|val_193| |401|val_401| |150|val_150| |273|val_273| |224|val_224| |369|val_369| | 66| val_66| |128|val_128| |213|val_213| +---+-------+ only showing top 20 rows
/*
Standalone 模式下使用内置 hive
-- 报错 原因:
metastore_db 只有master有
*/
scala> import spark.implicits._
import spark.implicits._
scala> spark.sql("create table kv1(key Int,value String)")
19/07/09 08:44:30 WARN HiveMetaStore: Location: file:/opt/module/spark/spark-warehouse/kv1 specified for non-external table:kv1
res0: org.apache.spark.sql.DataFrame = []
scala> spark.sql("load data local inpath 'examples/src/main/resources/kv1.txt' into table kv1")
res1: org.apache.spark.sql.DataFrame = []
scala> spark.sql("select * from kv1").show
19/07/09 08:45:14 WARN TaskSetManager: Lost task 0.0 in stage 0.0 (TID 0, 192.168.192.104, executor 2): java.io.FileNotFoundException: File file:/opt/module/spark/spark-warehouse/kv1/kv1.txt does not exist
at org.apache.hadoop.fs.RawLocalFileSystem.deprecatedGetFileStatus(RawLocalFileSystem.java:611)
at org.apache.hadoop.fs.RawLocalFileSystem.getFileLinkStatusInternal(RawLocalFileSystem.java:824)
at org.apache.hadoop.fs.RawLocalFileSystem.getFileStatus(RawLocalFileSystem.java:601)
at org.apache.hadoop.fs.FilterFileSystem.getFileStatus(FilterFileSystem.java:421)
at org.apache.hadoop.fs.ChecksumFileSystem$ChecksumFSInputChecker.<init>(ChecksumFileSystem.java:142)
at org.apache.hadoop.fs.ChecksumFileSystem.open(ChecksumFileSystem.java:346)
at org.apache.hadoop.fs.FileSystem.open(FileSystem.java:769)
at org.apache.hadoop.mapred.LineRecordReader.<init>(LineRecordReader.java:109)
at org.apache.hadoop.mapred.TextInputFormat.getRecordReader(TextInputFormat.java:67)
at org.apache.spark.rdd.HadoopRDD$$anon$1.liftedTree1$1(HadoopRDD.scala:252)
at org.apache.spark.rdd.HadoopRDD$$anon$1.<init>(HadoopRDD.scala:251)
at org.apache.spark.rdd.HadoopRDD.compute(HadoopRDD.scala:211)
at org.apache.spark.rdd.HadoopRDD.compute(HadoopRDD.scala:102)
at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:323)
at org.apache.spark.rdd.RDD.iterator(RDD.scala:287)
at org.apache.spark.rdd.MapPartitionsRDD.compute(MapPartitionsRDD.scala:38)
at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:323)
at org.apache.spark.rdd.RDD.iterator(RDD.scala:287)
at org.apache.spark.rdd.MapPartitionsRDD.compute(MapPartitionsRDD.scala:38)
at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:323)
at org.apache.spark.rdd.RDD.iterator(RDD.scala:287)
at org.apache.spark.rdd.MapPartitionsRDD.compute(MapPartitionsRDD.scala:38)
at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:323)
at org.apache.spark.rdd.RDD.iterator(RDD.scala:287)
at org.apache.spark.rdd.MapPartitionsRDD.compute(MapPartitionsRDD.scala:38)
at org.apache.spark.rdd.RDD.computeOrReadCheckpoint(RDD.scala:323)
at org.apache.spark.rdd.RDD.iterator(RDD.scala:287)
at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:87)
at org.apache.spark.scheduler.Task.run(Task.scala:99)
at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:322)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
+---+-------+
|key| value|
+---+-------+
|238|val_238|
| 86| val_86|
|311|val_311|
| 27| val_27|
|165|val_165|
|409|val_409|
|255|val_255|
|278|val_278|
| 98| val_98|
|484|val_484|
|265|val_265|
|193|val_193|
|401|val_401|
|150|val_150|
|273|val_273|
|224|val_224|
|369|val_369|
| 66| val_66|
|128|val_128|
|213|val_213|
+---+-------+
only showing top 20 rows
/*
Standalone 模式下使用内置 hive
*/
[lxl@hadoop102 spark]$ cp /opt/module/hadoop-2.7.2/etc/hadoop/core-site.xml ./conf/ [lxl@hadoop102 spark]$ cp /opt/module/hadoop-2.7.2/etc/hadoop/hdfs-site.xml ./conf/ [lxl@hadoop102 spark]$ xsync conf/ fname=conf pdir=/opt/module/spark ------------------- hadoop103 -------------- sending incremental file list conf/core-site.xml conf/docker.properties.template conf/fairscheduler.xml.template conf/hdfs-site.xml conf/log4j.properties.template conf/metrics.properties.template conf/slaves conf/spark-defaults.conf conf/spark-env.sh sent 4963 bytes received 358 bytes 10642.00 bytes/sec total size is 20089 speedup is 3.78 ------------------- hadoop104 -------------- sending incremental file list conf/core-site.xml conf/docker.properties.template conf/fairscheduler.xml.template conf/hdfs-site.xml conf/log4j.properties.template conf/metrics.properties.template conf/slaves conf/spark-defaults.conf conf/spark-env.sh sent 4963 bytes received 358 bytes 10642.00 bytes/sec total size is 20089 speedup is 3.78 [lxl@hadoop102 spark]$ ll 总用量 140 drwxr-xr-x 2 lxl lxl 4096 4月 26 2017 bin drwxr-xr-x 2 lxl lxl 4096 7月 9 08:48 conf drwxr-xr-x 5 lxl lxl 4096 4月 26 2017 data -rw-rw-r-- 1 lxl lxl 675 7月 9 08:22 derby.log drwxr-xr-x 4 lxl lxl 4096 4月 26 2017 examples drwxr-xr-x 2 lxl lxl 12288 7月 8 04:15 jars -rw-r--r-- 1 lxl lxl 17811 4月 26 2017 LICENSE drwxr-xr-x 2 lxl lxl 4096 4月 26 2017 licenses drwxrwxr-x 2 lxl lxl 4096 7月 9 08:21 logs drwxrwxr-x 5 lxl lxl 4096 7月 9 08:22 metastore_db -rw-r--r-- 1 lxl lxl 24645 4月 26 2017 NOTICE drwxrwxr-x 2 lxl lxl 4096 7月 8 03:12 object -rwxrwxrwx 1 lxl lxl 63 7月 7 06:10 pipe.sh drwxr-xr-x 8 lxl lxl 4096 4月 26 2017 python drwxr-xr-x 3 lxl lxl 4096 4月 26 2017 R -rw-r--r-- 1 lxl lxl 3817 4月 26 2017 README.md -rw-r--r-- 1 lxl lxl 128 4月 26 2017 RELEASE drwxr-xr-x 2 lxl lxl 4096 7月 6 10:06 sbin drwxrwxr-x 4 lxl lxl 4096 7月 9 08:44 spark-warehouse -rw-rw-r-- 1 lxl lxl 6010 7月 6 10:30 wordcount.jar drwxrwxr-x 8 lxl lxl 4096 7月 9 08:22 work drwxr-xr-x 2 lxl lxl 4096 4月 26 2017 yarn [lxl@hadoop102 spark]$ rm -rf spark-warehouse/ metastore_db/ derby.log [lxl@hadoop102 spark]$ ll 总用量 128 drwxr-xr-x 2 lxl lxl 4096 4月 26 2017 bin drwxr-xr-x 2 lxl lxl 4096 7月 9 08:48 conf drwxr-xr-x 5 lxl lxl 4096 4月 26 2017 data drwxr-xr-x 4 lxl lxl 4096 4月 26 2017 examples drwxr-xr-x 2 lxl lxl 12288 7月 8 04:15 jars -rw-r--r-- 1 lxl lxl 17811 4月 26 2017 LICENSE drwxr-xr-x 2 lxl lxl 4096 4月 26 2017 licenses drwxrwxr-x 2 lxl lxl 4096 7月 9 08:21 logs -rw-r--r-- 1 lxl lxl 24645 4月 26 2017 NOTICE drwxrwxr-x 2 lxl lxl 4096 7月 8 03:12 object -rwxrwxrwx 1 lxl lxl 63 7月 7 06:10 pipe.sh drwxr-xr-x 8 lxl lxl 4096 4月 26 2017 python drwxr-xr-x 3 lxl lxl 4096 4月 26 2017 R -rw-r--r-- 1 lxl lxl 3817 4月 26 2017 README.md -rw-r--r-- 1 lxl lxl 128 4月 26 2017 RELEASE drwxr-xr-x 2 lxl lxl 4096 7月 6 10:06 sbin -rw-rw-r-- 1 lxl lxl 6010 7月 6 10:30 wordcount.jar drwxrwxr-x 8 lxl lxl 4096 7月 9 08:22 work drwxr-xr-x 2 lxl lxl 4096 4月 26 2017 yarn
[lxl@hadoop102 spark]$ bin/spark-shell --master spark://hadoop102:7077 --conf spark.sql.warehouse.dir=hdfs://hadoop102:9000/spark
[lxl@hadoop102 resources]$ ll 总用量 36 -rw-r--r-- 1 lxl lxl 130 4月 26 2017 employees.json -rw-r--r-- 1 lxl lxl 240 4月 26 2017 full_user.avsc -rw-r--r-- 1 lxl lxl 5812 4月 26 2017 kv1.txt -rw-r--r-- 1 lxl lxl 73 4月 26 2017 people.json -rw-r--r-- 1 lxl lxl 32 4月 26 2017 people.txt -rw-r--r-- 1 lxl lxl 185 4月 26 2017 user.avsc -rw-r--r-- 1 lxl lxl 334 4月 26 2017 users.avro -rw-r--r-- 1 lxl lxl 615 4月 26 2017 users.parquet [lxl@hadoop102 resources]$ pwd /opt/module/spark/examples/src/main/resources [lxl@hadoop102 resources]$ hadoop fs -put kv1.txt /
scala> import spark.implicits._ import spark.implicits._ scala> spark.sql("create table kv(key Int,value String)") 19/07/09 09:09:35 WARN HiveMetaStore: Location: hdfs://hadoop102:9000/spark/kv specified for non-external table:kv res0: org.apache.spark.sql.DataFrame = [] scala> spark.sql("load data inpath '/kv1.txt' into table kv") 19/07/09 09:16:49 ERROR KeyProviderCache: Could not find uri with key [dfs.encryption.key.provider.uri] to create a keyProvider !! res1: org.apache.spark.sql.DataFrame = [] scala> spark.sql("select * from kv").show +---+-------+ |key| value| +---+-------+ |238|val_238| | 86| val_86| |311|val_311| | 27| val_27| |165|val_165| |409|val_409| |255|val_255| |278|val_278| | 98| val_98| |484|val_484| |265|val_265| |193|val_193| |401|val_401| |150|val_150| |273|val_273| |224|val_224| |369|val_369| | 66| val_66| |128|val_128| |213|val_213| +---+-------+ only showing top 20 rows
使用外置hive:
[lxl@hadoop102 spark]$ cp ../hive/conf/hive-site.xml ./conf/ [lxl@hadoop102 spark]$ ll conf/ 总用量 48 -rw-r--r-- 1 lxl lxl 1068 7月 9 08:48 core-site.xml -rw-r--r-- 1 lxl lxl 987 4月 26 2017 docker.properties.template -rw-r--r-- 1 lxl lxl 1105 4月 26 2017 fairscheduler.xml.template -rw-r--r-- 1 lxl lxl 1051 7月 9 08:48 hdfs-site.xml -rw-rw-r-- 1 lxl lxl 1466 7月 9 09:22 hive-site.xml -rw-r--r-- 1 lxl lxl 2025 4月 26 2017 log4j.properties.template -rw-r--r-- 1 lxl lxl 7313 4月 26 2017 metrics.properties.template -rw-r--r-- 1 lxl lxl 886 7月 6 09:56 slaves -rw-r--r-- 1 lxl lxl 1374 7月 7 00:55 spark-defaults.conf -rwxr-xr-x 1 lxl lxl 4280 7月 7 00:57 spark-env.sh [lxl@hadoop102 spark]$ xsync jars/mysql-connector-java-5.1.27-bin.jar fname=mysql-connector-java-5.1.27-bin.jar pdir=/opt/module/spark/jars ------------------- hadoop103 -------------- sending incremental file list mysql-connector-java-5.1.27-bin.jar sent 872508 bytes received 31 bytes 581692.67 bytes/sec total size is 872303 speedup is 1.00 ------------------- hadoop104 -------------- sending incremental file list mysql-connector-java-5.1.27-bin.jar sent 872508 bytes received 31 bytes 1745078.00 bytes/sec total size is 872303 speedup is 1.00
scala> spark.sql("create table kv1(key Int,value String)") 19/07/09 09:26:52 WARN HiveMetaStore: Location: hdfs://hadoop102:9000/user/hive/warehouse/kv1 specified for non-external table:kv1 res0: org.apache.spark.sql.DataFrame = [] scala> spark.sql("show databases").show +------------+ |databaseName| +------------+ | default| +------------+ scala> spark.sql("show tables").show +--------+--------------------+-----------+ |database| tableName|isTemporary| +--------+--------------------+-----------+ | default| dept| false| | default| emp| false| | default| gulivideo_category| false| | default| gulivideo_orc| false| | default| gulivideo_ori| false| | default| gulivideo_user_orc| false| | default| gulivideo_user_ori| false| | default|hive_hbase_emp_table| false| | default| kv1| false| | default| relevance_hbase_emp| false| | default| staff_hive| false| +--------+--------------------+-----------+
[lxl@hadoop102 spark]$ bin/spark-s spark-shell spark-sql spark-submit [lxl@hadoop102 spark]$ bin/spark-sql log4j:WARN No appenders could be found for logger (org.apache.hadoop.util.Shell). log4j:WARN Please initialize the log4j system properly. log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info. Using Spark's default log4j profile: org/apache/spark/log4j-defaults.properties 19/07/09 09:33:51 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable 19/07/09 09:33:51 INFO HiveMetaStore: 0: Opening raw store with implemenation class:org.apache.hadoop.hive.metastore.ObjectStore 19/07/09 09:33:51 INFO ObjectStore: ObjectStore, initialize called ...
...
spark-sql (default)> show tables; 19/07/09 09:34:40 INFO SparkSqlParser: Parsing command: show tables 19/07/09 09:34:43 INFO HiveMetaStore: 0: get_database: default ...
... database tableName isTemporary default dept false default emp false default gulivideo_category false default gulivideo_orc false default gulivideo_ori false default gulivideo_user_orc false default gulivideo_user_ori false default hive_hbase_emp_table false default kv1 false default relevance_hbase_emp false default staff_hive false Time taken: 3.787 seconds, Fetched 11 row(s) 19/07/09 09:34:44 INFO CliDriver: Time taken: 3.787 seconds, Fetched 11 row(s)
去除 spark-sql 中的日志:
[lxl@hadoop102 conf]$ pwd /opt/module/spark/conf [lxl@hadoop102 conf]$ mv log4j.properties.template log4j.properties [lxl@hadoop102 conf]$ vi log4j.properties
log4j.properties:
# Set everything to be logged to the console
log4j.rootCategory=error, console
日志信息消除后:
[lxl@hadoop102 spark]$ bin/spark-sql spark-sql (default)> show databases; databaseName default Time taken: 3.022 seconds, Fetched 1 row(s) spark-sql (default)> select * from emp > ; empno ename job mgr hiredate sal comm deptno 7369 SMITH CLERK 7902 1980-12-17 800.0 NULL 20 7499 ALLEN SALESMAN 7698 1981-2-20 1600.0 300.0 30 7521 WARD SALESMAN 7698 1981-2-22 1250.0 500.0 30 7566 JONES MANAGER 7839 1981-4-2 2975.0 NULL 20 7654 MARTIN SALESMAN 7698 1981-9-28 1250.0 1400.0 30 7698 BLAKE MANAGER 7839 1981-5-1 2850.0 NULL 30 7782 CLARK MANAGER 7839 1981-6-9 2450.0 NULL 10 7788 SCOTT ANALYST 7566 1987-4-19 3000.0 NULL 20 7839 KING PRESIDENT NULL 1981-11-17 5000.0 NULL 10 7844 TURNER SALESMAN 7698 1981-9-8 1500.0 0.0 30 7876 ADAMS CLERK 7788 1987-5-23 1100.0 NULL 20 7900 JAMES CLERK 7698 1981-12-3 950.0 NULL 30 7902 FORD ANALYST 7566 1981-12-3 3000.0 NULL 20 7934 MILLER CLERK 7782 1982-1-23 1300.0 NULL 10 Time taken: 1.105 seconds, Fetched 14 row(s) spark-sql (default)>
请你一定不要停下来 成为你想成为的人
感谢您的阅读,我是LXL