澳门新葡萄京娱乐网站-澳门新葡萄京888官网-所有平台

热门关键词: 澳门新葡萄京娱乐网站,澳门新葡萄京888官网

MySQL客商和权力管理,SQL见怪不怪4种多少源详细

作者: MySQL数据库  发布:2019-12-22

前言

通用load/write方法

MySQL用户权限表

MySQL的认证是“用户”加“主机”而权限是访问资源对象,MySQL服务器通过权限表来控制用户对数据库的访问,权限表存放在mysql数据库中,由mysql_install_db脚本初始化。存储账户权限信息表主要有:user,db,tables_priv,columns_priv,procs_priv这五张表(5.6之前还有host表,现在已经把host内容整合进user表),五张表其含义分别是:

user表

user表时MySQL中最重要的一个权限表,记录允许连接到服务器的账号信息,里面的权限是全局级的。例如:一个用户在user表中被授予了DELETE权限,则该用户可以删除MySQL服务器上所有数据库的任何记录。MySQL5.6中user表有43个字段,这些字段大概可以分为4类,分别是用户列、权限列、安全列和资源控制列,详细解释如下图:

图片 1

db表

db表存储了用户对某个数据库的操作权限,决定用户能从哪个主机存储哪个数据库。User表中存储了某个主机对数据库的操作权限,配置和db权限表对给定主机上数据库级操作权限做更细致的控制。这个权限表不受GRANT和REVOKE语句的影响,字段大致可以分为两类:用户列和权限列,详细解释如下图:

图片 2

tables_priv and columns_priv表

tables_priv表用来对表设置操作权限,有8个字段分别是Host、Db、User、Table_name、Grantor、Timestamp、Table_priv和Column_priv,各个字段说明如下:

1)  Host、Db、User和Table_name4个字段分表示主机名、数据库名、用户名和表明。

2)  Grantor表示修改该记录的用户。

3)  Timestamp字段表示修改该记录的时间。

4)  Table_priv表示对表的操作权限包括、select、insert、update、delete、create、drop、grant、references、index和alter。

5)  Column_priv字段表示对表中的列的操作权限,包括select、insert、update和references。

procs_priv表

存储过程和存储函数相关的权限,分别是Host、Db、User、Routine_name、Routine_type、Grantor、Proc_priv和Timestamp,各个字段的说明如下:

1)  Host、Db和User字段分别表示主机名、数据库名和用户名。Routine_name表示存储过程或函数的名称。

2)  Routine_type表示存储过程或函数的类型。

3)  Routine_type字段有两个值,分别是FUNCTION和PROCEDURE。FUNCTION表示这是一个函数;PROCEDURE表示这是一个存储过程。

4)  Grantor是插入或修改该记录的用户。

5)  Proc_priv表示拥有的权限,包括Execute、Alter Routine、Grant这3种。

6) Timestamp表示记录更新时间。

关于mysql的权限简单的理解就是mysql允许你做你全力以内的事情,不可以越界。

手动指定选项

MySQL访问控制两阶段

阶段1:客户端连接核实阶段

阶段2:客户端操作核实阶段

客户端连接核实阶段,当连接MySQL服务器时,服务器基于用户的身份以及用户是否能通过正确的密码身份验证,来接受或拒绝连接。即客户端用户连接请求中会提供用户名称、主机地址和密码,MySQL使用user表中的三个字段(Host、User、Password)执行身份检查,服务器只有在user表记录的Host和User字段匹配客户端主机名和用户名,并且提供正确的面貌时才接受连接。如果连接核实没有通过,服务器完全拒绝访问;否则,服务器接受连接,然后进入阶段2等待用户请求。

客户端操作核实阶段,当客户端的连接请求被MySQL服务器端通过其身份认证后。那么接下来就可以发送数据库的操作命令给服务器端处理,服务器检查用户要执行的操作,在确认权限时,MySQL首先检查user表,如果指定的权限没有在user表中被授权;MySQL将检查db表,db表时下一安全层级,其中的权限限定于数据库层级,在该层级的SELECT权限允许用户查看指定数据库的所有表中的数据;如果在该层级没有找到限定的权限,则MySQL继续检查tables_priv表以及columns_priv表,如果所有权限表都检查完毕,但还是没有找到允许的权限操作,MySQL将返回错误信息,用户请求的操作不能执行,操作失败。其过程大概如下图:

图片 3

权限控制,对于MLSQL而言的重要程度可以说是生命线。 MLSQL需要面对各式各样的资源访问,比如MySQL, Oracle,HDFS,Hive,Kafka,Sorl,ElasticSearch,Redis,API,Web等等,不同用户对这些数据源的权限是不一样的。

Spark SQL的DataFrame接口支持多种数据源的操作。一个DataFrame可以进行RDDs方式的操作,也可以被注册为临时表。把DataFrame注册为临时表之后,就可以对该DataFrame执行SQL查询。

MySQL用户及密码管理

MySQL提供许多语句用来管理用户账号,这些语句可以用来管理包括登陆和退出MySQL服务器、创建用户、删除用户、密码管理和权限管理等内容。MySQL数据库的安全性,需要通过账户管理来保证。下面介绍四种用来管理账号密码的方式:

1)通过Mysqladmin工具(只能改密码)

 

1
2
3
4
5
# 给root@localhost用户登录mysql设置密码为"redhat";
$ mysqladmin -u root -h localhost password "redhat"
 
# 修改root@localhost用户登录Mysql数据库的密码;
$ mysqladmin -u root -h localhost password "new passwd" -p "old passwd"

2)通过CREATE USER语句

 

1
Mysql> create user 'USERNAME'@'HOST' identified by 'PASSWORD';

创建登录用户,MySQL的登录用户必须是’USERNAME’@’HOST’(用户名加主机名),如’mysql’@’172.16.16.1′,含义是只有在172.16.16.1这台主机上才可以使用mysql用户登录MySQL数据库(还可以指定只允许登录那个数据库)。

HOST的表现方式:

  1. IP地址,如172.16.16.1;

2. 主机名,如localhost;

3. 网络地址,如172.16.0.0

4. 通配符,如

%:匹配任意字符

_:匹配任意单个字符如172.16.16._(允许172.16.16.1-172.16.16.9)

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 创建用户允许任何主机登录以USERNAME用户登录;
Mysql> create user 'USERNAME'@'%' identified by 'PASSWORD';  
 
# 更改用户名;
Mysql> rename user OLD_NAME to NEW_NAME;
 
# 删除登录用户;
Mysql> drop user 'USERNAME'@'HOST';
 
# 删除MySQL默认的无用账户;
mysql> drop user 'root'@'localhost.localdomain';
 
# 删除MySQL默认的无用账户;
mysql> drop user 'root'@'127.0.0.1';

3)通过直接修改mysql.user表的用户记录

 

1
Mysql> update mysql.user set password=PASSWORD('redhat') where user='root';

 

1
2
# 给root@localhost用户登录mysql设置密码为redhat并以加密方式存放;
Mysql> set password for 'root'@'localhost'=PASSWORD('redhat');

 

 

1
2
# 刷新内存中现有的表
Mysql> flush privileges;

4)通过GRANT指令(只能用于添加新用户)

 

1
2
# 创建tom用户并对此库下的所有表赋予所有权限;
Mysql> grant all on DB_NAME.* to 'tom'@'localhost' identified by '1234';

虽然介绍了好几种方法创建用户,但真正在使用中,最好直接使用GRANT或者CREATE USER语句,而不要直接将用户信息插入user表中,因为user表中存储了全局级别的权限以及其他的账户信息,如果意外破坏了user表中的记录,则可能会对MySQL服务器造成很大的影响。

传统模式是,每个用户都需要有个proxy user,然后到每个数据源里面给这个proxy user进行授权。 这看起来似乎就是麻烦点,但是在实际操作中,基本是很难执行的,不同的数据源在不同的团队里面,那么整个申请流程可能要天甚至周计了。如果上面的问题已经让人气馁,那么对于采用Hive做数仓的公司,可能对HIve权限访问更让人绝望。Hive的授权模式是跟着Linux用户走的,也就是Spark启动用户是谁,谁就有权限访问,这个对于多租户的MLSQL应用来说,则是完全不可行了,比如启动Spark的是sparkUser,但是真正执行的人,其实可能是张三,李四等等。Hive就无法知道是具体哪个人完成的,只知道是sparkUser。

Spark SQL的默认数据源为Parquet格式。数据源为Parquet文件时,Spark SQL可以方便的执行所有的操作。

MySQL管理员密码找回

1)关闭MySQL

 

 

1
$ service mysqld stop

2)在配置文件中[mysqld]字段添加skip-grant-tables指令,跳过授权表

 

1
2
3
$ cat /etc/my.cnf
[mysqld]
skip-grant-tables

3)给root用户登录mysql设置密码为redhat并以加密方式

 

1
2
Mysql> use mysql;
Mysql> update user set password=PASSWORD('redhat') where user='root';

MySQL5.7修改密码

 

1
Mysql> update mysql.user set authentication_string=PASSWORD('redhat') where user='root';

 

还有一个大家可能感慨的点:

修改配置项spark.sql.sources.default,可修改默认数据源格式。

MySQL权限管理

权限管理主要是对登录到MySQL的用户进行权限验证,所有用户的权限都存储在MySQL的权限表中,不合理的权限规划会给MySQL服务器带来安全隐患。数据库管理员要对所有用户的权限进行合理规划管理。MySQL权限系统的主要功能时证实连接到一台给定主机的用户,并且赋予该用户在数据库上的SELECT/INSERT/UPDATE和DELETE权限。

一、MySQL权限说明

账户权限信息被存储在MySQL数据库的几张权限表中,在MySQL启动时,服务器将这些数据库表中权限信息的内容读入内存。其中GRANT和REVOKE语句所涉及的常用权限大致如下这些:CREATE、DROP、SELECT、INSERT、UPDATE、DELETE、INDEX、ALTER、CREATE、ROUTINE、FILE等,还有一个特殊的proxy权限,是用来赋予某个用户具有给他人赋予权限的权限。

二、MySQL用户授权

授权就是为某个用户授予权限,合理的授权可以保证数据库的安全,MySQL中可以使用GRANT语句为用户授予权限。授权可以分为多个层次:

全局层级:全局权限适用于一个给定服务器中的所有数据库,这些权限存储在mysql.user表中。

数据库层级:数据库权限适用于一个给定数据库中的所有目标,这些权限存储在mysql.db表中。

表层级:表权限适用于一个给定表中的所有列,这些权限存储在mysql.tables_priv表中。

列层级:列权限使用于一个给定表中的单一列,这些权限存储在mysql.columns_priv表中。

子程序层级:CREATE ROUTINE、ALTER ROUTINE、EXECUTE和GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且,除了CREATE ROUTINE外,这些权限可以被授予子程序层级,并存储在mysql.procs_priv表中。

PS:MySQL中必须拥有GRANT权限的用户才可以执行GRANT语句。

1)GRANT赋予用户权限

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 定义对已经存在的用户可以操作此库下的所有表及所有权限;
Mysql> grant all privileges on DB_NAME.* to 'USERNAME'@'HOST';
 
# 创建tom用户并赋予select权限对此库下的所有表;
Mysql> grant select on DB_NAME.* to 'tom'@'localhost' identified by '1234';
 
# 定义tom用户赋予insert权限对db库下的xsb表;
Mysql> grant insert on db.xsb to 'tom'@'localhost';
 
# 定义tom用户赋予update权限对db库下的xsb表;                      
Mysql> grant update on db.xsb to 'tom'@'localhost';
 
# 定义tom用于赋予update权限对db库下的xsb表中的AGE字段;
Mysql> grant update(AGE) on db.xsb to 'tom'@'localhost';
 
# 定义tom用于赋予super权限在*.*上(super权限可以对全局变量更改);
Mysql> grant super on *.* to 'tom'@'%';
 
# 通过GRANT语句中的USAGE权限,你可以创建账户而不授予任何权限;它可以将所有全局权限设为'N',假定你将在以后将具体权限授予该账户;
Mysql> grant usage on *.* to 'tom'@'%';

all表示赋予用户全部权限(包含存储过程、存储函数等创建和执行)。当数据库名称.表名称被*.*代替,表示赋予用户操作服务器上所有数据库所有表的权限。用户地址可以是localhost,也可以是ip地址、机器名字、域名。也可以用’%’表示从任何地址连接。而’连接口令’不能为空,否则创建失败。

2)REVOKE移除用户权限

 

1
2
3
4
5
# 移除tom用户对于db.xsb的权限;
Mysql> revoke all on db.xsb from 'tom'@'localhost';
 
# 刷新授权表;
Mysql> flush privileges;

3)SHOW查看用户的权限

 

1
Mysql> show grants for 'USERNAME'@'HOST';

PS:使用REVOKE收回权限之后,用户帐户的记录将从db、host、tables_priv、columns_priv表中删除,但是用户帐号记录依然在user表中保存。

三、PROXY特殊权限

如果想让某个用户具有给他人赋予权限的能力,那么就需要proxy权限了。当你给一个用户赋予all权限之后,你查看mysql.user表会发现Grant_priv字段还是为N,表示其没有给他人赋予权限的权限。

我们可以查看一下系统默认的超级管理员权限:

 

1
2
3
4
5
6
7
8
mysql> show grants for 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

可以看到其本身有PROXY权限,并且这个语句跟一般授权语句还不太一样。所以如果想让一个远程用户有给他人赋予权限的能力,就需要给此用户PROXY权限,如下:

 

1
2
mysql> grant all on *.* to 'test'@'%' identified by '123456';
mysql> GRANT PROXY ON ''@'' TO 'test'@'%' WITH GRANT OPTION;

四、数据库开发人员,创建表、索引、视图、存储过程、函数等权限授权

grant创建、修改、删除MySQL数据表结构权限

 

1
2
3
grant create on testdb.* to developer@'192.168.0.%';
grant alter on testdb.* to developer@'192.168.0.%';
grant drop on testdb.* to developer@'192.168.0.%';

grant操作MySQL外键权限

 

1
grant references on testdb.* to developer@'192.168.0.%';

grant操作MySQL临时表权限。

 

1
grant create temporary tables on testdb.* to developer@'192.168.0.%';

grant操作MySQL索引权限

 

1
grant index on testdb.* to developer@'192.168.0.%';

grant操作MySQL视图、查看视图源代码 权限

 

1
2
grant create view on testdb.* to developer@'192.168.0.%';
grant show view on testdb.* to developer@'192.168.0.%';

grant操作MySQL存储过程、存储函数权限

 

1
2
3
grant create routine on testdb.* to developer@'192.168.0.%';
grant alter routine on testdb.* to developer@'192.168.0.%';
grant execute on testdb.* to developer@'192.168.0.%';

等等。。。

 

我们好不容易写了个脚本,跑了一个小时,突然脚本失败,一看,第350行那里访问的数据源权限不足。 这可真是让人恼火。

scala> val df = spark.read.load("hdfs://hadoop001:9000/namesAndAges.parquet")df: org.apache.spark.sql.DataFrame = [age: bigint, name: string]scala> df.select.write.save

问题来了

当数据源格式不是parquet格式文件时,需要手动指定数据源的格式。数据源格式需要指定全名(例如:org.apache.spark.sql.parquet),如果数据源格式为内置格式,则只需要指定简称json, parquet, jdbc, orc, libsvm, csv, text来指定数据的格式。

那么,怎么才能在脚本运行前,就知道脚本里涉及到的资源是不是都被授权了?

可以通过SparkSession提供的read.load方法用于通用加载数据,使用write和save保存数据。

答案是:有

scala> val peopleDF = spark.read.format.load("hdfs://hadoop001:9000/people.json")peopleDF: org.apache.spark.sql.DataFrame = [age: bigint, name: string]scala> peopleDF.write.format.save("hdfs://hadoop001:9000/namesAndAges.parquet")scala>

题外话:标题不严谨,因为MLSQL本质是个解释性执行语言,不需要编译,更好的标题是 。

除此之外,可以直接运行SQL在文件上:

MLSQL如果开启了权限验证,他会先扫描整个脚本,然后提取必要的信息,这些信息就包含了各种数据源的详细信息,从而在运行前就可以知道你是不是访问了未经授权的库表。那么MLSQL是怎么做到的呢?我们来看下面的信息:

val sqlDF = spark.sql("SELECT * FROM parquet.`hdfs://hadoop001:9000/namesAndAges.parquet`")sqlDF.show()
connect jdbc wheredriver="com.mysql.jdbc.Driver"and url="jdbc:mysql://${ip}:${host}/db1?${MYSQL_URL_PARAMS}"and user="${user}"and password="${password}"as db1_ref;load jdbc.`db1_ref .people`as people;save append people as jdbc.`db1_ref.spam` ;

文件保存选项

因为MLSQL要求任何数据源,都需要使用load语句进行加载,在解析load语句时,MLSQL知道,用户现在要访问的是基于JDBC协议的数据源访问,他通过url拿到了这些信息:

可以采用SaveMode执行存储操作,SaveMode定义了对数据的处理模式。需要注意的是,这些保存模式不使用任何锁定,不是原子操作。此外,当使用Overwrite方式执行时,在输出新数据之前原数据就已经被删除。SaveMode详细介绍如下表:

db: db1table: peopleoperateType: loadsourceType: mysqltableType: JDBC

Scala/Java

当然,这个脚本用户还会写入一张spam表,也一样会被提取信息:

Any Language

db: db1table: peopleoperateType: savesourceType: mysqltableType: JDBC

Meaning

然后还有一张临时表people,所以这个脚本总共有三张表信息,之后这些信息会被发送到AuthCenter里进行判断,AuthCenter会告诉MLSQL那张表是没有对当前用户授权的,如果发现未经授权的表,MLSQL会直接抛出异常。整个过程中,完全不会执行任何物理计划,只是对脚本的信息抽取。

SaveMode.ErrorIfExists “error” 如果文件存在,则报错 SaveMode.Append “append” 追加 SaveMode.Overwrite “overwrite” 覆写 SaveMode.Ignore “ignore” 数据存在,则忽略

在MLSQL中,我们不能在select语句里访问hive表,只能通过load语句加载,比如下面的句子会报错:

Parquet文件

select * from public.abc as table1;

Parquet格式经常在Hadoop生态圈中被使用,它也支持Spark SQL的全部数据类型。Spark SQL 提供了直接读取和存储 Parquet 格式文件的方法。

我们无权在select语句中访问public.abc库,如果需要使用,你可以通过如下方式完成:

// Encoders for most common types are automatically provided by importing spark.implicits._import spark.implicits._val peopleDF = spark.read.json("examples/src/main/resources/people.json")// DataFrames can be saved as Parquet files, maintaining the schema informationpeopleDF.write.parquet("hdfs://hadoop001:9000/people.parquet")// Read in the parquet file created above// Parquet files are self-describing so the schema is preserved// The result of loading a Parquet file is also a DataFrameval parquetFileDF = spark.read.parquet("hdfs://hadoop001:9000/people.parquet")// Parquet files can also be used to create a temporary view and then used in SQL statementsparquetFileDF.createOrReplaceTempViewval namesDF = spark.sql("SELECT name FROM parquetFile WHERE age BETWEEN 13 AND 19")namesDF.map(attributes => "Name: " + attributes// +------------+// | value|// +------------+// |Name: Justin|// +------------+
load hive.`public.abc ` as abc;select * from abc as table1;

解析分区信息

如何实现列级别控制

对表进行分区是对数据进行优化的方式之一。在分区的表内,数据通过分区列将数据存储在不同的目录下。Parquet数据源现在能够自动发现并解析分区信息。例如,对人口数据进行分区存储,分区列为gender和country,使用下面的目录结构:

MLSQL在解析load语句的时候,会询问当前用户访问的表,有哪些列是被授权的,然后会改写最后load的语句,提供一个新的视图,该视图只有用户被授权的列。

path└── to└── table├── gender=male│ ├── ...│ ││ ├── country=US│ │ └── data.parquet│ ├── country=CN│ │ └── data.parquet│ └── ...└── gender=female├── ...│├── country=US│ └── data.parquet├── country=CN│ └── data.parquet└── ...

总结

通过传递path/to/table给 SQLContext.read.parque

MLSQL通过一些有效的限制,可以在语法解析层面直接提取了所有数据源相关信息,并且将其发送给到配套的权限中心进行判断,避免在运行时发现授权拒绝问题。MLSQL此举意义重大,使得MLSQL系统不再完全依赖于底层系统的权限控制,从而让问题得到了极大的简化。

或SQLContext.read.load,Spark SQL将自动解析分区信息。

好了,以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对脚本之家的支持。

返回的DataFrame的Schema如下:

root|-- name: string |-- age: long |-- gender: string |-- country: string 

需要注意的是,数据的分区列的数据类型是自动解析的。当前,支持数值类型和字符串类型。自动解析分区类型的参数为:

spark.sql.sources.partitionColumnTypeInference.enabled

如果想关闭该功能,直接将该参数设置为disabled。此时,分区列数据格式将被默认设置为string类型,不再进行类型解析。

Schema合并

像ProtocolBuffer、Avro和Thrift那样,Parquet也支持Schema evolution。用户可以先定义一个简单的Schema,然后逐渐的向Schema中增加列描述。通过这种方式,用户可以获取多个有不同Schema但相互兼容的Parquet文件。现在Parquet数据源能自动检测这种情况,并合并这些文件的schemas。

因为Schema合并是一个高消耗的操作,在大多数情况下并不需要,所以Spark SQL从1.5.0开始默认关闭了该功能。可以通过下面两种方式开启该功能:

当数据源为Parquet文件时,将数据源选项mergeSchema设置为true。

spark.sql.parquet.mergeSchema为true。

// sqlContext from the previous example is used in this example.// This is used to implicitly convert an RDD to a DataFrame.import spark.implicits._// Create a simple DataFrame, stored into a partition directoryval df1 = sc.makeRDD.map.toDFdf1.write.parquet("hdfs://hadoop001:9000/data/test_table/key=1")// Create another DataFrame in a new partition directory,// adding a new column and dropping an existing columnval df2 = sc.makeRDD.map.toDFdf2.write.parquet("hdfs://hadoop001:9000/data/test_table/key=2")// Read the partitioned tableval df3 = spark.read.option("mergeSchema", "true").parquet("hdfs://hadoop001:9000/data/test_table")df3.printSchema()// The final schema consists of all 3 columns in the Parquet files together// with the partitioning column appeared in the partition directory paths.// root// |-- single: int // |-- double: int // |-- triple: int // |-- key : int 

Hive数据源

Apache Hive是Hadoop上的SQL引擎,Spark SQL编译时可以包含Hive支持,也可以不包含。包含Hive支持的Spark SQL可以支持Hive表访问、UDF以及 Hive 查询语言等。需要强调的 一点是,如果要在Spark SQL中包含Hive的库,并不需要事先安装Hive。一般来说,最好还是在编译Spark SQL时引入Hive支持,这样就可以使用这些特性了。如果你下载的是二进制版本的 Spark,它应该已经在编译时添加了 Hive 支持。

若要把Spark SQL连接到一个部署好的Hive上,你必须把hive-site.xml复制到 Spark的配置文件目录中。即使没有部署好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.Fileimport org.apache.spark.sql.Rowimport org.apache.spark.sql.SparkSessioncase class Record(key: Int, value: String)// warehouseLocation points to the default location for managed databases and tablesval warehouseLocation = new File.getAbsolutePathval spark = SparkSession.builder().appName.config("spark.sql.warehouse.dir", warehouseLocation).enableHiveSupportimport spark.implicits._import spark.sqlsql("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 HiveQLsql.show()// +---+-------+// |key| value|// +---+-------+// |238|val_238|// | 86| val_86|// |311|val_311|// ...// Aggregation queries are also supported.sql FROM src").show()// +--------+// |count|// +--------+// | 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.map(i => RecordrecordsDF.createOrReplaceTempView// 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|// ...

内嵌Hive应用

如果要使用内嵌的Hive,什么都不用做,直接用就可以了。 –conf :

spark.sql.warehouse.dir=

注意:如果你使用的是内部的Hive,在Spark2.0之后,spark.sql.warehouse.dir用于指定数据仓库的地址,如果你需要是用HDFS作为路径,那么需要将core-site.xml和hdfs-site.xml 加入到Spark conf目录,否则只会创建master节点上的warehouse目录,查询时会出现文件找不到的问题,这是需要向使用HDFS,则需要将metastore删除,重启集群。

外部Hive应用

如果想连接外部已经部署好的Hive,需要通过以下几个步骤。

a 将Hive中的hive-site.xml拷贝或者软连接到Spark安装目录下的conf目录下。

b 打开spark shell,注意带上访问Hive元数据库的JDBC客户端。

$ bin/spark-shell --master spark://hadoop001:7077 --jars mysql-connector-java-5.1.27-bin.jar

JSON数据集

Spark SQL 能够自动推测 JSON数据集的结构,并将它加载为一个Dataset[Row]. 可以通过SparkSession.read.json()去加载一个 Dataset[String]或者一个JSON 文件.注意,这个JSON文件不是一个传统的JSON文件,每一行都得是一个JSON串。

{"name":"Michael"}{"name":"Andy", "age":30}{"name":"Justin", "age":19}// Primitive types  and Product types  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 filesval path = "examples/src/main/resources/people.json"val peopleDF = spark.read.json// The inferred schema can be visualized using the printSchema() methodpeopleDF.printSchema()// root// |-- age: long // |-- name: string // Creates a temporary view using the DataFramepeopleDF.createOrReplaceTempView// SQL statements can be run by using the sql methods provided by sparkval 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 stringval otherPeopleDataset = spark.createDataset("""{"name":"Yin","address":{"city":"Columbus","state":"Ohio"}}""" :: Nil)val otherPeople = spark.read.jsonotherPeople.show()// +---------------+----+// | address|name|// +---------------+----+// |[Columbus,Ohio]| Yin|// +---------------+----+

JDBC

Spark SQL可以通过JDBC从关系型数据库中读取数据的方式创建DataFrame,通过对DataFrame一系列的计算后,还可以将数据再写回关系型数据库中。

注意,需要将相关的数据库驱动放到spark的类路径下。

$ bin/spark-shell --master spark://hadoop001:7077 --jars mysql-connector-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 sourceval jdbcDF = spark.read.format.option("url", "jdbc:mysql://hadoop001:3306/rdd").option("dbtable", " rddtable").option.option.load()val connectionProperties = new Properties()connectionProperties.putconnectionProperties.putval jdbcDF2 = spark.read.jdbc("jdbc:mysql://hadoop001:3306/rdd", "rddtable", connectionProperties)// Saving data to a JDBC sourcejdbcDF.write.format.option("url", "jdbc:mysql://hadoop001:3306/rdd").option("dbtable", "rddtable2").option.option.save()jdbcDF2.write.jdbc("jdbc:mysql://hadoop001:3306/mysql", "db", connectionProperties)// Specifying create table column data types on writejdbcDF.write.option("createTableColumnTypes", "name CHAR, comments VARCHAR.jdbc("jdbc:mysql://hadoop001:3306/mysql", "db", connectionProperties)

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持脚本之家。

本文由澳门新葡萄京娱乐网站发布于MySQL数据库,转载请注明出处:MySQL客商和权力管理,SQL见怪不怪4种多少源详细

关键词: