• Archive by category "数据库"

Blog Archives

RSQLite数据库编程指南

R的极客理想系列文章,涵盖了R的思想,使用,工具,创新等的一系列要点,以我个人的学习和体验去诠释R的强大。

R语言作为统计学一门语言,一直在小众领域闪耀着光芒。直到大数据的爆发,R语言变成了一门炙手可热的数据分析的利器。随着越来越多的工程背景的人的加入,R语言的社区在迅速扩大成长。现在已不仅仅是统计领域,教育,银行,电商,互联网….都在使用R语言。

要成为有理想的极客,我们不能停留在语法上,要掌握牢固的数学,概率,统计知识,同时还要有创新精神,把R语言发挥到各个领域。让我们一起动起来吧,开始R的极客理想。

关于作者:

  • 张丹,分析师/程序员/Quant: R,Java,Nodejs
  • blog: http://blog.fens.me
  • email: bsspirit@gmail.com

转载请注明出处:
http://blog.fens.me/r-rsqlite/

前言

RSQLite包支持在R中的非常方便地创建和使用sqlite数据库,sqlite是单文件数据库,不需要预先安装,可内嵌在程序代码中。RSQLite的主要数据库操作依赖于DBI包,DBI是R语言中关系型数据库的底层接口包,除了支持RSQLite, 还支持 RMySQL, RPostgreSQL等连接其他数据库的包。

RSQLite的使用,是不需要本地提前装好SQLite的。本文阅读时,可以跳过第1,2节直接看RSQLite安装和使用。

目录

  1. SQLite数据库介绍
  2. 在 Windows 上安装 SQLite
  3. RSQLite 安装和使用

1. SQLite数据库介绍

SQLite 是一个开源的、轻量级的单进程数据库,通过单文件实现数据库存储。SQLite 数据库无服务器端、零配置的、支持事务性的 SQL 数据库引擎,应用程序可以进行静态或动态连接,直接访问其存储文件。SQLite 源代码不受版权限制。

SQLite官方网站:https://www.sqlite.org/index.html

2. 在 Windows 上安装 SQLite

如果单独使用 SQLite 时,我们可以从官方网站下载页面,从 Windows 区下载预编译的二进制文件和命令行工具文件。

把上面2个目录的文件,进行解压,统一放到一个目录下面,我放到了D:/sqlite目录。

双击sqlite3.exe文件,用来启动命令行工具。

最后,添加 D:\sqlite 到 PATH 环境变量,就可以直接在命令提示符下,使用 sqlite3 命令,可以在任意目录启动sqlite数据库。

我们也可以安装sqlite可视化工具,进行操作,如免费的sqlitestudio工具https://www.sqlitestudio.pl/

3. RSQLite安装和使用

在R语言中,我们也可以非常方便地使用RSQLite数据库,本地多个文件,也不用再单独装个MySQL之类的,对于数据量不太大的应用来说,是非常方便的,特别是结合shiny的原型小应用。RSQLite的使用,不需求本地提前装好RSQLite的程序。

RSQLite 大部分功能都是依赖于DBI包的,在DBI之外,RSQLite包支持事务,数据库拷贝,修改配置等功能。话说RSQLite 也是Hadley Wickham开发的。RSQLite 包的 API列表:https://rsqlite.r-dbi.org/reference/index.html

4.1 RSQLite安装
首先,我们安装RSQLite是非常简单的,就一条语句

# 安装RSQLite包
> install.packages("RSQLite")

# 加载RSQLite包
> library(RSQLite)

RSQLite 数据库就是一个文件,所以我们首先要,先设置一个工作路径,然后给定义一个文件名。在目录中,会多出一个文件 C:\work\R\db\my-db.sqlite,这样我们的数据库就算是建立好了。


> setwd("C:\\work\\R\\db")
> conn <- dbConnect(SQLite(), "my-db.sqlite")
> dbGetInfo(conn)
$db.version
[1] "3.39.4"

$dbname
[1] "C:\\work\\R\\db\\my-db.sqlite"

$username
[1] NA

$host
[1] NA

$port
[1] NA

建立连接后,数据库的所有操作,都在内存中进行,并不会实时同步到文件,只有在执行关闭数据库连接的时候,才会一次性写入本地数据库的文件中。

4.2 整表操作
接下来,我们用RSQLite来创建数据表,通过整个表的方式,创建表 mtcars,并把数据集mtcars整体写入表,读取表。


# 查看数据集
> head(mtcars)
                   mpg cyl disp  hp drat    wt  qsec vs am gear carb
Mazda RX4         21.0   6  160 110 3.90 2.620 16.46  0  1    4    4
Mazda RX4 Wag     21.0   6  160 110 3.90 2.875 17.02  0  1    4    4
Datsun 710        22.8   4  108  93 3.85 2.320 18.61  1  1    4    1
Hornet 4 Drive    21.4   6  258 110 3.08 3.215 19.44  1  0    3    1
Hornet Sportabout 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2
Valiant           18.1   6  225 105 2.76 3.460 20.22  1  0    3    1

# 创建表 mtcars,数据整表写入
> dbWriteTable(conn, "mtcars", head(mtcars))

# 查看当前数据库中的表
> dbListTables(conn)
[1] "mtcars"

# 数据整表读取
> dbReadTable(conn,"mtcars")
   mpg cyl disp  hp drat    wt  qsec vs am gear carb
1 21.0   6  160 110 3.90 2.620 16.46  0  1    4    4
2 21.0   6  160 110 3.90 2.875 17.02  0  1    4    4
3 22.8   4  108  93 3.85 2.320 18.61  1  1    4    1
4 21.4   6  258 110 3.08 3.215 19.44  1  0    3    1
5 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2
6 18.1   6  225 105 2.76 3.460 20.22  1  0    3    1

# 用SQL读取数据
> dbGetQuery(conn, 'SELECT * FROM mtcars where cyl < 6')
   mpg cyl disp hp drat   wt  qsec vs am gear carb
1 22.8   4  108 93 3.85 2.32 18.61  1  1    4    1

# 用预定义变量的SQL读取数据
> dbGetQuery(conn, 'SELECT * FROM mtcars WHERE mpg < :x',
+            params = list(x = 20))
   mpg cyl disp  hp drat   wt  qsec vs am gear carb
1 18.7   8  360 175 3.15 3.44 17.02  0  0    3    2
2 18.1   6  225 105 2.76 3.46 20.22  1  0    3    1

# 删除表
> dbRemoveTable(conn,"mtcars")

4.3 增删改查独立语句
按照每一步创建表,和执行CRUD操作的进行独立语句的执行。

创建表,用一个已知的data.frame数据集,进行表的创建,只会创建表结构,不会写入数据集的数据。


# 创建表
> dbCreateTable(conn,"table2",mtcars)

# 查看表的字段
> dbListFields(conn,"table2")
 [1] "mpg"  "cyl"  "disp" "hp"   "drat" "wt"   "qsec" "vs"   "am"  
[10] "gear" "carb"

# 读取表,没有数据
> dbReadTable(conn,"table2")
 [1] mpg  cyl  disp hp   drat wt   qsec vs   am   gear carb
<0 行> (或0-长度的row.names)

用SQL的INSERT INTO语法插入数据。


# 拼接SQL
> sql1<-"INSERT INTO table2 VALUES ("
> sql2<-paste(table1[1,],collapse = ",")
> sql3<-")"
> sql<-paste0(sql1,sql2,sql3);
> sql<-paste0(sql1,sql2,sql3);
> sql
[1] "INSERT INTO table2 VALUES (21,6,160,110,3.9,2.62,16.46,0,1,4,4)"

# 执行插入
> dbExecute(conn,sql)
[1] 1

# 查看数据
> dbReadTable(conn,"table2")
  mpg cyl disp  hp drat   wt  qsec vs am gear carb
1  21   6  160 110  3.9 2.62 16.46  0  1    4    4

使用SQL的UPDATE语法修改数据


# 把cyl改成改成17
> dbExecute(conn,"UPDATE table2 SET cyl=17 WHERE mpg=21",immediate = TRUE)
[1] 1

# 查看数据
> dbReadTable(conn,"table2")
  mpg cyl disp  hp drat   wt  qsec vs am gear carb
1  21  17  160 110  3.9 2.62 16.46  0  1    4    4

用SQL的DELETE语法,删除数据。


# 再插入一条数据
> dbExecute(conn,sql)
[1] 1
> dbReadTable(conn,"table2")
  mpg cyl disp  hp drat   wt  qsec vs am gear carb
1  21  17  160 110  3.9 2.62 16.46  0  1    4    4
2  21   6  160 110  3.9 2.62 16.46  0  1    4    4

# 执行删除数据
> dbExecute(conn, 'DELETE FROM table2 WHERE cyl=17')
[1] 1
> dbReadTable(conn,"table2")
  mpg cyl disp  hp drat   wt  qsec vs am gear carb
1  21   6  160 110  3.9 2.62 16.46  0  1    4    4

4.4 执行完成
执行完成,关闭数据库连接。


> # 关闭连接
> dbDisconnect(conn)

这个时候,我们刚才的操作才会同步写入到 my-db.sqlite 文件中,之前一直都是在内存中执行的。
4.5 批量查询
RSQLite还提供了批量查询功能,对于数据量很大的时候,可以采用批量的方式,分步加载数据。


# 建立数据库连接
> conn <- dbConnect(SQLite(), "my-db.sqlite")

# 执行延迟查询,获得查询的句柄rs
> rs <- dbSendQuery(conn, 'SELECT * FROM mtcars')

# 以分页批次方式获取数据,每页为5条
> while (!dbHasCompleted(rs)) {
+   df <- dbFetch(rs, n = 5)
+   print(df)
+ }
   mpg cyl disp  hp drat    wt  qsec vs am gear carb
1 21.0   6  160 110 3.90 2.620 16.46  0  1    4    4
2 21.0   6  160 110 3.90 2.875 17.02  0  1    4    4
3 22.8   4  108  93 3.85 2.320 18.61  1  1    4    1
4 21.4   6  258 110 3.08 3.215 19.44  1  0    3    1
5 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2
   mpg cyl  disp  hp drat   wt  qsec vs am gear carb
1 18.1   6 225.0 105 2.76 3.46 20.22  1  0    3    1
2 14.3   8 360.0 245 3.21 3.57 15.84  0  0    3    4
3 24.4   4 146.7  62 3.69 3.19 20.00  1  0    4    2
4 22.8   4 140.8  95 3.92 3.15 22.90  1  0    4    2
5 19.2   6 167.6 123 3.92 3.44 18.30  1  0    4    4
   mpg cyl  disp  hp drat   wt  qsec vs am gear carb
1 17.8   6 167.6 123 3.92 3.44 18.90  1  0    4    4
2 16.4   8 275.8 180 3.07 4.07 17.40  0  0    3    3
3 17.3   8 275.8 180 3.07 3.73 17.60  0  0    3    3
4 15.2   8 275.8 180 3.07 3.78 18.00  0  0    3    3
5 10.4   8 472.0 205 2.93 5.25 17.98  0  0    3    4
   mpg cyl  disp  hp drat    wt  qsec vs am gear carb
1 10.4   8 460.0 215 3.00 5.424 17.82  0  0    3    4
2 14.7   8 440.0 230 3.23 5.345 17.42  0  0    3    4
3 32.4   4  78.7  66 4.08 2.200 19.47  1  1    4    1
4 30.4   4  75.7  52 4.93 1.615 18.52  1  1    4    2
5 33.9   4  71.1  65 4.22 1.835 19.90  1  1    4    1
   mpg cyl  disp  hp drat    wt  qsec vs am gear carb
1 21.5   4 120.1  97 3.70 2.465 20.01  1  0    3    1
2 15.5   8 318.0 150 2.76 3.520 16.87  0  0    3    2
3 15.2   8 304.0 150 3.15 3.435 17.30  0  0    3    2
4 13.3   8 350.0 245 3.73 3.840 15.41  0  0    3    4
5 19.2   8 400.0 175 3.08 3.845 17.05  0  0    3    2
   mpg cyl  disp  hp drat    wt qsec vs am gear carb
1 27.3   4  79.0  66 4.08 1.935 18.9  1  1    4    1
2 26.0   4 120.3  91 4.43 2.140 16.7  0  1    5    2
3 30.4   4  95.1 113 3.77 1.513 16.9  1  1    5    2
4 15.8   8 351.0 264 4.22 3.170 14.5  0  1    5    4
5 19.7   6 145.0 175 3.62 2.770 15.5  0  1    5    6
   mpg cyl disp  hp drat   wt qsec vs am gear carb
1 15.0   8  301 335 3.54 3.57 14.6  0  1    5    8
2 21.4   4  121 109 4.11 2.78 18.6  1  1    4    2

# 关闭查询句柄
> dbClearResult(rs)

4.6 预编译参数使用
通过dbBind可以使用预编译参数,防止SQL注入,也是需要用到dbSendQuery()的延迟查询,通过dbFetch()来获得数据。


> rs <- dbSendQuery(conn, 'SELECT * FROM mtcars WHERE mpg  < :x')
> dbBind(rs, params = list(x = 15))
> dbFetch(rs)
   mpg cyl disp  hp drat    wt  qsec vs am gear carb
1 14.3   8  360 245 3.21 3.570 15.84  0  0    3    4
2 10.4   8  472 205 2.93 5.250 17.98  0  0    3    4
3 10.4   8  460 215 3.00 5.424 17.82  0  0    3    4
4 14.7   8  440 230 3.23 5.345 17.42  0  0    3    4
5 13.3   8  350 245 3.73 3.840 15.41  0  0    3    4
> dbClearResult(rs)

我们可以进行多参数绑定查询,查询mpg在20到30之间以0.2为间隔的数据。


> rs <- dbSendQuery(conn, 'SELECT * FROM mtcars WHERE mpg = :x')
> dbBind(rs, params = list(x = seq(20, 30, by = 0.2)))
> dbFetch(rs)
   mpg cyl  disp  hp drat    wt  qsec vs am gear carb
1 21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4
2 21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4
3 21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1
4 21.4   4 121.0 109 4.11 2.780 18.60  1  1    4    2
5 22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1
6 22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2
7 24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2
8 26.0   4 120.3  91 4.43 2.140 16.70  0  1    5    2
> dbClearResult(rs)
> dbDisconnect(conn) 

RSQLite的使用起来是非常简单地,这样我们可以避免小应用每次都需要安装数据库,一个独立的数据库文件什么都能做,轻巧简单!

转载请注明出处:
http://blog.fens.me/r-rsqlite/

打赏作者

RPostgreSQL数据库编程指南

R的极客理想系列文章,涵盖了R的思想,使用,工具,创新等的一系列要点,以我个人的学习和体验去诠释R的强大。

R语言作为统计学一门语言,一直在小众领域闪耀着光芒。直到大数据的爆发,R语言变成了一门炙手可热的数据分析的利器。随着越来越多的工程背景的人的加入,R语言的社区在迅速扩大成长。现在已不仅仅是统计领域,教育,银行,电商,互联网….都在使用R语言。

要成为有理想的极客,我们不能停留在语法上,要掌握牢固的数学,概率,统计知识,同时还要有创新精神,把R语言发挥到各个领域。让我们一起动起来吧,开始R的极客理想。

关于作者:

  • 张丹,分析师/程序员/Quant: R,Java,Nodejs
  • blog: http://fens.me
  • email: bsspirit@gmail.com

转载请注明出处:
http://blog.fens.me/r-rpostgressql/

前言

PostgreSQL和R通常可以一起用于数据分析,PostgreSQL作为数据库引擎,R作为统计工具。在R语言中,有2个包可以直接通过R连接PostgreSQL数据库,分别是RPostgreSQL包和RPostgres包,本文将先介绍用RPostgreSQL包进行直接连接PostgreSQL数据库。间接的连接方法还有RJDBC,RODBC等,以后再有专门的文章进行介绍。

PostgreSQL的数据库安装和基本信息,请参考文章在Ubuntu上安装PostgreSQL

目录

  1. RPostgreSQL包介绍
  2. 安装RPostgreSQL包
  3. RPostgreSQL包的API使用
  4. window客户端环境的中文乱码解决方案

1. RPostgreSQL包介绍

RPostgreSQL一个R语言程序包,由官方提供访问 PostgreSQL数据库的R语言接口程序,RPostgreSQL主要依赖于DBI项目,以访问 PostgreSQL 数据库系统。CRAN中的项目地址
https://cran.r-project.org/web/packages/RPostgreSQL/index.html

2. 安装RPostgreSQL包

RPostgreSQL包的安装过程非常简单,就是一条语句就能够搞定了,不管是在Window环境还是Linux环境。

在Linux Ubuntu中,需要先安装libpq-dev系统库,不然会出现libpq-fe.h错误


In file included from RS-PQescape.c:7:
RS-PostgreSQL.h:23:14: fatal error: libpq-fe.h: No such file or directory
   23 | #    include "libpq-fe.h"
      |              ^~~~~~~~~~~~

安装libpq-dev系统库


~ sudo apt install libpq-dev

安装和加载RPostgreSQL包


> install.packages("RPostgreSQL")
> library(RPostgreSQL)

3. RPostgreSQL包的API使用

RPostgreSQL包,提供了很多的API函数,API的使用过程,我们可以分成6个部分来介绍。

  • 3.1 数据库连接
  • 3.2 数据库状态查看
  • 3.3 数据直接查询
  • 3.4 数据交互查询
  • 3.5 数据批量插入
  • 3.6 事务处理

3.1 建立数据库链接


# 加载PostgreSQL驱动
> drv = dbDriver("PostgreSQL") 

# 建立数据库连接
> con1 = dbConnect(drv,  dbname="testdb",user = "test",  password = "test", host ="192.168.1.5")

3.2 数据库状态查看


# 再建立2个数据库连接
> con2 = dbConnect(drv,  dbname="testdb",user = "test",  password = "test", host ="192.168.1.5")
> con3 = dbConnect(drv,  dbname="testdb",user = "test",  password = "test", host ="192.168.1.5")

查看驱动状态


> dbGetInfo(drv)
$drvName
[1] "PostgreSQL"

$connectionIds                   # 连接1
$connectionIds[[1]]
<postgresqlconnection>

$connectionIds[[2]]              # 连接2
<postgresqlconnection>

$connectionIds[[3]]              # 连接3
<postgresqlconnection>

$fetch_default_rec
[1] 500

$managerId
<postgresqldriver>

$length
[1] 16

$num_con
[1] 3

$counter
[1] 9

查看con1数据连接状态


> dbGetInfo(con1)
$host
[1] "192.168.1.5"

$port
[1] "5432"

$user
[1] "test"

$dbname
[1] "testdb"

$serverVersion
[1] "12.0.4"

$protocolVersion
[1] 3

$backendPId
[1] 27263

$rsId
list()

关闭连接


# 关闭连接1
> dbDisconnect(con1)
[1] TRUE

# 查看con1状态
> isPostgresqlIdCurrent(con1)
[1] FALSE

# 查睦con1信息
> dbGetInfo(con1)
Error in postgresqlConnectionInfo(dbObj, ...) : 
  expired PostgreSQLConnection dbObj

关闭全部连接


# 通过循环关闭全部连接
> for(con in dbListConnections(drv)){
+   dbDisconnect(con)
+ }

# 查询驱动状态,没有活动连接
> dbGetInfo(drv)
$drvName
[1] "PostgreSQL"

$connectionIds   # 没有活动连接
list()

$fetch_default_rec
[1] 500

$managerId
<postgresqldriver>

$length
[1] 16

$num_con
[1] 0

$counter
[1] 9

3.3 数据直接查询

数据直接查询可以通过直接读取整个表数据dbReadTable()函数,或者执行查询语句dbGetQuery()函数来实现。

建立新连接


> con1 = dbConnect(drv,  dbname="testdb",user = "test",  password = "test", host ="192.168.1.5")

查看数据库中,所有的表。


# 所有表的列表
> dbListTables(con1)
[1] "account" "iris"  

# 确认表名是否存在
> dbExistsTable(con1,"iris")
[1] TRUE

通过表名,读取整个表的数据


> dbReadTable(con1,"iris")
  Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1          5.1         3.5          1.4         0.2  setosa
2          4.9         3.0          1.4         0.2  setosa
3          4.7         3.2          1.3         0.2  setosa
4          4.6         3.1          1.5         0.2  setosa
5          5.0         3.6          1.4         0.2  setosa

通过SQL语句,读取表中的数据


> dbGetQuery(con1,  "SELECT * FROM iris")
  row.names Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1         1          5.1         3.5          1.4         0.2  setosa
2         2          4.9         3.0          1.4         0.2  setosa
3         3          4.7         3.2          1.3         0.2  setosa
4         4          4.6         3.1          1.5         0.2  setosa
5         5          5.0         3.6          1.4         0.2  setosa

3.4 数据交互查询

数据交互查询,可以通过发送一条sql请求dbSendQuery()函数来实现,返回数据访问句柄,再用dbFetch()函数来分批次获取数据。


# 发送SQL
> rs = dbSendQuery(con1,  statement  =  "SELECT * FROM iris")

# 判断rs句柄是否完成
> dbHasCompleted(rs)
[1] FALSE

# 取数据n=-1时,取全表数据
> dbFetch(rs,n=-1)
  row.names Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1         1          5.1         3.5          1.4         0.2  setosa
2         2          4.9         3.0          1.4         0.2  setosa
3         3          4.7         3.2          1.3         0.2  setosa
4         4          4.6         3.1          1.5         0.2  setosa
5         5          5.0         3.6          1.4         0.2  setosa

# 判断rs句柄是否完成
> dbHasCompleted(rs)
[1] TRUE

# 取完数据后,句柄rs就失效了
> dbFetch(rs,n=1)
data frame with 0 columns and 0 rows

我们在获得句柄后,可以查到关于句柄的详细信息。


# 发送SQL,获得句柄rs
> rs = dbSendQuery(con1,  statement  =  "SELECT * FROM iris")

# 查看rs信息
> dbGetInfo(rs)
$statement
[1] "SELECT * FROM iris"

$isSelect
[1] 1

$rowsAffected
[1] -1

$rowCount
[1] 0

$completed
[1] 0

$fieldDescription
$fieldDescription[[1]]
$fieldDescription[[1]]$name
[1] "row.names"    "Sepal.Length" "Sepal.Width"  "Petal.Length" "Petal.Width"  "Species"     

$fieldDescription[[1]]$Sclass
[1] 16 14 14 14 14 16

$fieldDescription[[1]]$type
[1]  25 701 701 701 701  25

$fieldDescription[[1]]$len
[1] -1  8  8  8  8 -1

$fieldDescription[[1]]$precision
[1] -1 -1 -1 -1 -1 -1

$fieldDescription[[1]]$scale
[1] -1 -1 -1 -1 -1 -1

$fieldDescription[[1]]$nullOK
[1] TRUE TRUE TRUE TRUE TRUE TRUE

# 查看执行SQL语句
> dbGetStatement(rs)
[1] "SELECT * FROM iris"

# 查看数据结构
> dbColumnInfo(rs)
          name    Sclass   type len precision scale nullOK
1    row.names character   TEXT  -1        -1    -1   TRUE
2 Sepal.Length    double FLOAT8   8        -1    -1   TRUE
3  Sepal.Width    double FLOAT8   8        -1    -1   TRUE
4 Petal.Length    double FLOAT8   8        -1    -1   TRUE
5  Petal.Width    double FLOAT8   8        -1    -1   TRUE
6      Species character   TEXT  -1        -1    -1   TRUE

# 查看已获取的行
> dbGetRowCount(rs)
[1] 0

# 获取2行
> ret1 <- dbFetch(rs, 2)
> ret1
  row.names Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1         1          5.1         3.5          1.4         0.2  setosa
2         2          4.9         3.0          1.4         0.2  setosa

# 查看已获取的行
> dbGetRowCount(rs)
[1] 2

# 查看句柄是否完成
> dbHasCompleted(rs)
[1] FALSE

# 获取剩余的行
> ret2 <- dbFetch(rs, -1)
> ret2
  row.names Sepal.Length Sepal.Width Petal.Length Petal.Width Species
3         3          4.7         3.2          1.3         0.2  setosa
4         4          4.6         3.1          1.5         0.2  setosa
5         5          5.0         3.6          1.4         0.2  setosa

# 查看句柄是否完成
> dbHasCompleted(rs)
[1] TRUE

# 清空rs句柄
> dbClearResult(rs)
[1] TRUE

# 拼接完整数据集
> rbind(ret1,ret2)
  row.names Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1         1          5.1         3.5          1.4         0.2  setosa
2         2          4.9         3.0          1.4         0.2  setosa
3         3          4.7         3.2          1.3         0.2  setosa
4         4          4.6         3.1          1.5         0.2  setosa
5         5          5.0         3.6          1.4         0.2  setosa

3.5 数据批量插入
R语言中,数据组织方式是用data.frame的,data.frame可以批量地进行数据操作,在操作数据库时,我们也可以直接用data.frame与PostgreSQL数据库进行这种批量的操作。

当数据库没表时,dbWriteTable()函数会新建一个数据表。


# 新建数据表iris-table,插入5条数据
> dbWriteTable(con1,'iris-table',iris[1:5,])
[1] TRUE

# 读取数据表
> dbReadTable(con1,'iris-table')
  Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1          5.1         3.5          1.4         0.2  setosa
2          4.9         3.0          1.4         0.2  setosa
3          4.7         3.2          1.3         0.2  setosa
4          4.6         3.1          1.5         0.2  setosa
5          5.0         3.6          1.4         0.2  setosa

再进行数据表插入,由于表已存在,所以提示不能插入,如果需求追加插入时,可以增加append的合并参数。


# 插入数据失败
> dbWriteTable(con1,'iris-table',iris[6:10,])
[1] FALSE
Warning message:
In postgresqlWriteTable(conn, name, value, ...) :
  table iris-table exists in database: aborting assignTable

# 追加插入数据
> dbWriteTable(con1,'iris-table',iris[6:10,],append=TRUE)
[1] TRUE

# 查看数据表,变成了10行。
> dbReadTable(con1,'iris-table')
   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1           5.1         3.5          1.4         0.2  setosa
2           4.9         3.0          1.4         0.2  setosa
3           4.7         3.2          1.3         0.2  setosa
4           4.6         3.1          1.5         0.2  setosa
5           5.0         3.6          1.4         0.2  setosa
6           5.4         3.9          1.7         0.4  setosa
7           4.6         3.4          1.4         0.3  setosa
8           5.0         3.4          1.5         0.2  setosa
9           4.4         2.9          1.4         0.2  setosa
10          4.9         3.1          1.5         0.1  setosa

当表不需要后,可以直接删除数据表


# 删除数据表
> dbRemoveTable(con1,"iris-table")
[1] TRUE

# 删除后,数据表读取失败
> dbReadTable(con1,'iris-table')
Error in postgresqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not Retrieve the result : ERROR:  relation "iris-table" does not exist
LINE 1: SELECT * from "iris-table"
                      ^
)
Error in names(out) <- make.names(names(out), unique = TRUE) : 
  NULL是不能有属性的
此外: Warning message:
In postgresqlQuickSQL(conn, statement, ...) :
  Could not create execute: SELECT * from "iris-table"

3.6 事务提交和回滚
RPostgreSQL包的事务控制,是通过dbSendQuery()函数,配合事务控制dbBegin()函数,dbRollback()函数,dbCommit()函数来完成的。

我们模拟一个事务的场景,当删除一条数据时,判断这条数据影响多少行,如果删除超过1行则不执行删除进行回滚,如果正好为1行,则进行删除。


# 查看数据集,共5行
> df1<-dbGetQuery(con1, "select * from iris");df1
  row.names Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1         1          5.1         3.5          1.4         0.2  setosa
2         2          4.9         3.0          1.4         0.2  setosa
3         3          4.7         3.2          1.3         0.2  setosa
4         4          4.6         3.1          1.5         0.2  setosa
5         5          5.0         3.6          1.4         0.2  setosa


# 开始事务
> dbBegin(con1)
[1] TRUE

# 进行删除
> rs <- dbSendQuery(con1, "Delete from iris where \"Species\" = 'setosa'")

# 删除判断,影响大于1行则回滚,
> if(dbGetInfo(rs, what = "rowsAffected") > 1){
+   warning("Rolling back transaction")
+   dbRollback(con1)
+ }else{
+   dbCommit(con1)
+ }
[1] TRUE
Warning message:
Rolling back transaction           # 进行回滚

# 再查询数据表结果,未发生删除
> dbGetQuery(con1, "select count(*) from iris")
  count
1     5

4. window客户端环境的中文乱码解决方案

执行查询,出现中文乱码。我们就需要逐步排查,到底是哪个环节产生了乱码,统一把编码以UTF-8做为编码标准。


# 创建中文data.frame
> acc<-data.frame(
+   user_id=1:3,
+   useranme=c("a1","小明","粉丝日志"),
+   password=c("678fdaiufda","jfdaked21+_~!","E!SM<I*")
+ )

# 建表,插入数据
> dbWriteTable(con1,"acc",acc)
[1] TRUE

# 读取数据
> df3<-dbReadTable(con1,"acc");df3
  user_id     useranme      password
1       1           a1   678fdaiufda
2       2       灏忔槑 jfdaked21+_~!
3       3 绮変笣鏃ュ織       E!SM<I*

我们插入的是正确,但读出来的数据是乱码。我们用pgadmin客户端,检查一下数据表,验证一下数据库中数据是否显示正常。

接下来,再检查一下PostgreSQL数据库中testdb库的编码为UTF8。

在服务器上,启动psql查询客户端。


# 启动psql客户端
~ sudo -u postgres psql

# 检查数据库
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | test=CTc/postgres
(4 rows)

再检查一下,客户端通信的字符编码,也是UTF8是正常的。


> dbGetQuery(con1, "SHOW CLIENT_ENCODING")
  client_encoding
1            UTF8

最后,考虑到Windows环境下,R的客户端默认编码为GBK,所以我们再试一下,把数据在R中进行编码转型。


# 字符编码转型
> df3a <- as.data.frame(apply(df3, 2, function(x){
+   iconv(x, 'UTF-8', 'GBK')
+ }))

# 查看输出结果
> df3a
  user_id useranme      password
1       1       a1   678fdaiufda
2       2     小明 jfdaked21+_~!
3       3 粉丝日志       E!SM<I*

这样就可以解决了,中文乱码的问题了。

我们已经完成,掌握了RPostgreSQL包的各种使用技巧,希望大家理解原理后,能少犯错误,提高工作效率!

转载请注明出处:
http://blog.fens.me/r-postgressql/

打赏作者

在Ubuntu上安装PostgreSQL

Ubuntu实用工具系列文章,将介绍基于Linux ubuntu的各种工具软件的配置和使用。有些工具大家早已耳熟能详,有些工具经常用到但确依然陌生。我将记录我在使用操作系统时,安装及配置工具上面的一些方法,把使用心得记录下来也便于自己的以后查找和回忆。

关于作者:

  • 张丹,分析师/程序员/Quant: R,Java,Nodejs
  • blog: http://fens.me
  • email: bsspirit@gmail.com

转载请注明出处:
http://blog.fens.me/linux-postgresql-install/

前言

PostgreSQL是一个功能非常强大的、源代码开放关系型数据库,我之前主要在用MySQL,为了知识的完整性,也把PostgreSQL练一练。介绍一下PostgreSQL数据库在Ubuntu上的安装过程,和基本的使用操作。大家也可以对比MySQL的安装过程,请参考文章在Ubuntu中安装MySQL

希望本文能让所有的PostgreSQL新手快速上手,顺利完成PostgreSQL数据库的初探。

目录

  1. PostgreSQL在Ubuntu中安装
  2. 数据库操作命令
  3. 用pgadmin远程访问数据库

1. PostgreSQL在Ubuntu中安装

本文使用的Linux是Ubuntu 20.04.1 LTS Server 64bit的系统,安装PostgreSQL数据库软件包可以通过apt实现。

1.1 下载和安装PostgreSQL

在Linux Ubuntu中安装 PostgreSQL 数据库,可以先使用apt查看一下PostgreSQL的版本支持。apt支持的PostgreSQL数据库的最新版本为12,目前来说是主流的。PostgreSQL官方在 2020-09-24 发布了,最新的版本PostgreSQL 13

# 查看Postgres支持
~  apt show postgresql
Package: postgresql
Version: 12+214ubuntu0.1
Priority: optional
Section: database
Source: postgresql-common (214ubuntu0.1)
Origin: Ubuntu
Maintainer: Ubuntu Developers <ubuntu-devel-discuss@lists.ubuntu.com>
Original-Maintainer: Debian PostgreSQL Maintainers <team+postgresql@tracker.debian.org>
Bugs: https://bugs.launchpad.net/ubuntu/+filebug
Installed-Size: 67.6 kB
Depends: postgresql-12
Suggests: postgresql-doc
Task: postgresql-server
Download-Size: 3,924 B
APT-Sources: http://cn.archive.ubuntu.com/ubuntu focal-updates/main amd64 Packages
Description: object-relational SQL database (supported version)
 This metapackage always depends on the currently supported PostgreSQL
 database server version.
 .
 PostgreSQL is a fully featured object-relational database management
 system.  It supports a large part of the SQL standard and is designed
 to be extensible by users in many aspects.  Some of the features are:
 ACID transactions, foreign keys, views, sequences, subqueries,
 triggers, user-defined types and functions, outer joins, multiversion
 concurrency control.  Graphical user interfaces and bindings for many
 programming languages are available as well.

N: There is 1 additional record. Please use the '-a' switch to see it

我们可以直接使用apt的源进行安装,一条命令就够了。


# 安装Postgres服务器端
~ sudo apt install postgresql

安装完成后,查看系统进程情况,和服务器端口占用情况

# 检查postgresql服务器系统进程
~ ps -aux|grep postgresql
postgres   18145  0.0  0.1 215652 29396 ?        Ss   10:30   0:00 /usr/lib/postgresql/12/bin/postgres -D /var/lib/postgresql/12/main -c config_file=/etc/postgresql/12/main/postgresql.conf

# 检查postgresql服务器占用端口,默认为5432
~  netstat -nlt|grep 5432
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN

通过系统服务命令检查 postgresql 服务器状态


~  service postgresql status
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Fri 2020-11-06 10:30:38 UTC; 12min ago
   Main PID: 17860 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 18660)
     Memory: 0B
     CGroup: /system.slice/postgresql.service

Nov 06 10:30:38 conan systemd[1]: Starting PostgreSQL RDBMS...
Nov 06 10:30:38 conan systemd[1]: Finished PostgreSQL RDBMS.

1.2 设置 postgres 账户密码

安装 PostgreSQL 数据库后,会自动创建 Ubuntu 的 postgres 用户和 PostgreSQL 的 postgres 用户。为了后续让测试更方便,我们可以设置这两个账户的密码保持一致,都为 postgres。

首先,修改Ubuntu系统中,postgres用户的密码为postgres。


# 清除原有 postgres账户密码
~ sudo passwd -d postgres
passwd: password expiry information changed.

# 设置新密码 postgres 
~ sudo -u postgres passwd
New password:
Retype new password:
passwd: password updated successfully

然后,登陆到PostgreSQL数据库,修改postgres用户的密码为postgres 。


#  登陆psql客户端
~ sudo -u postgres psql
psql (12.4 (Ubuntu 12.4-0ubuntu0.20.04.1))
Type "help" for help.

postgres=# ALTER USER postgres WITH PASSWORD 'postgres'
postgres-# \q

完成数据库中 postgres 账户的密码修改。

1.3 初始化PostgreSQL数据库空间

我们自己需要创建数据库空间,这里与MySQL是不同的,MySQL没有这一步骤。PostgreSQL 官方文档推荐的位置是 /user/local/pgsql/data 或 /var/lib/pgsql/data。那么,我们也把数据库空间放到/user/local/pgsql/data 目录吧。

首先要创建目录,各种安装过程中的问题从此处开始。

# 创建目录,-p 表示创建多级目录
~ sudo mkdir -p /usr/local/pgsql/data

# 设置权限
~ sudo chown postgres -R /usr/local/pgsql

# 创建数据库空间
~ sudo -u postgres initdb -D /user/local/pgsql/data
sudo: initdb: command not found

问题一:没有 initdb 命令。

我们从根目录搜索一下,查找initdb的命令位置。

~ sudo find / -name initdb
/usr/lib/postgresql/12/bin/initdb

指定initdb的位置后,再重新创建数据库空间,创建成功。

~ sudo -u postgres /usr/lib/postgresql/12/bin/initdb -D /usr/local/pgsql/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /usr/local/pgsql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/lib/postgresql/12/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start

1.4 启动数据库
启动数据库,执行上面最后一个提示的命令即可,pg_ctl是用于初始化PostgreSQL数据库集群。

~ sudo -u postgres /usr/lib/postgresql/12/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
waiting for server to start..../bin/sh: 1: cannot create logfile: Permission denied
 stopped waiting
pg_ctl: could not start server
Examine the log output.

问题二:pg_ctl权限问题

又出现了一个问题Permission denied,权限问题。增加输出目录/var/lib/postgresql/到logfile ,再次执行命令,程序正常启动!

~ sudo -u postgres /usr/lib/postgresql/12/bin/pg_ctl -D /usr/local/pgsql/data -l /var/lib/postgresql/logfile start
waiting for server to start.... done
server started

2. 数据库操作命令

安装postgresql服务器完成后,会自动地一起安装postgresql命令行客户端程序 psql。

在本机输入psql命令就可以启动,客户端程序访问postgresql服务器,直接输入 psql 命令时,会提示当前的用户没有授权。

~ psql
psql: error: could not connect to server: FATAL:  role "conan" does not exist

要使用postgres 用户,启动psql程序

~ sudo -u postgres psql
psql (12.4 (Ubuntu 12.4-0ubuntu0.20.04.1))
Type "help" for help.

postgres=#

如果一切正常,系统提示符会变为 postgres=# ,表示这时已经进入了数据库控制台。

2.1 控制台的命令使用

接下来的操作命令都在控制台内完成了,我们先要了解一下控制台,支持哪些操作。psql程序有一些不属于SQL命令的内部命令。它们以反斜线开头,“\”。

控制台命令列表:

  • \password 命令(设置密码)
  • \q 命令(退出)
  • \h:查看SQL命令的解释,比如\h select。
  • \?:查看psql命令列表。
  • \l:列出所有数据库。
  • \c [database_name]:连接其他数据库。
  • \d:列出当前数据库的所有表格。
  • \d [table_name]:列出某一张表格的结构。
  • \du:列出所有用户。
  • \e:打开文本编辑器。
  • \conninfo:列出当前数据库和连接的信息。

我们都分别试一下,这些命令。

查看当前数据库信息

postgres-# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

查看数据库和操作系统版本

postgres=# SELECT version();
                                                            version
-------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.4 (Ubuntu 12.4-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-10ubuntu2) 9.3.0, 64-bit
(1 row)

查看SQL命令解释


postgres=# \h
Available help:
  ABORT                            ALTER SYSTEM                     CREATE FOREIGN DATA WRAPPER      CREATE USER MAPPING              DROP ROUTINE                     PREPARE
  ALTER AGGREGATE                  ALTER TABLE                      CREATE FOREIGN TABLE             CREATE VIEW                      DROP RULE                        PREPARE TRANSACTION
  ALTER COLLATION                  ALTER TABLESPACE                 CREATE FUNCTION                  DEALLOCATE                       DROP SCHEMA                      REASSIGN OWNED
  ALTER CONVERSION                 ALTER TEXT SEARCH CONFIGURATION  CREATE GROUP                     DECLARE                          DROP SEQUENCE                    REFRESH MATERIALIZED VIEW
  ALTER DATABASE                   ALTER TEXT SEARCH DICTIONARY     CREATE INDEX                     DELETE                           DROP SERVER                      REINDEX
  ALTER DEFAULT PRIVILEGES         ALTER TEXT SEARCH PARSER         CREATE LANGUAGE                  DISCARD                          DROP STATISTICS                  RELEASE SAVEPOINT
  ALTER DOMAIN                     ALTER TEXT SEARCH TEMPLATE       CREATE MATERIALIZED VIEW         DO                               DROP SUBSCRIPTION                RESET
  ALTER EVENT TRIGGER              ALTER TRIGGER                    CREATE OPERATOR                  DROP ACCESS METHOD               DROP TABLE                       REVOKE
  ALTER EXTENSION                  ALTER TYPE                       CREATE OPERATOR CLASS            DROP AGGREGATE                   DROP TABLESPACE                  ROLLBACK
  ALTER FOREIGN DATA WRAPPER       ALTER USER                       CREATE OPERATOR FAMILY           DROP CAST                        DROP TEXT SEARCH CONFIGURATION   ROLLBACK PREPARED
  ALTER FOREIGN TABLE              ALTER USER MAPPING               CREATE POLICY                    DROP COLLATION                   DROP TEXT SEARCH DICTIONARY      ROLLBACK TO SAVEPOINT
  ALTER FUNCTION                   ALTER VIEW                       CREATE PROCEDURE                 DROP CONVERSION                  DROP TEXT SEARCH PARSER          SAVEPOINT
  ALTER GROUP                      ANALYZE                          CREATE PUBLICATION               DROP DATABASE                    DROP TEXT SEARCH TEMPLATE        SECURITY LABEL
  ALTER INDEX                      BEGIN                            CREATE ROLE                      DROP DOMAIN                      DROP TRANSFORM                   SELECT
  ALTER LANGUAGE                   CALL                             CREATE RULE                      DROP EVENT TRIGGER               DROP TRIGGER                     SELECT INTO
  ALTER LARGE OBJECT               CHECKPOINT                       CREATE SCHEMA                    DROP EXTENSION                   DROP TYPE                        SET
  ALTER MATERIALIZED VIEW          CLOSE                            CREATE SEQUENCE                  DROP FOREIGN DATA WRAPPER        DROP USER                        SET CONSTRAINTS
  ALTER OPERATOR                   CLUSTER                          CREATE SERVER                    DROP FOREIGN TABLE               DROP USER MAPPING                SET ROLE
  ALTER OPERATOR CLASS             COMMENT                          CREATE STATISTICS                DROP FUNCTION                    DROP VIEW                        SET SESSION AUTHORIZATION
  ALTER OPERATOR FAMILY            COMMIT                           CREATE SUBSCRIPTION              DROP GROUP                       END                              SET TRANSACTION
  ALTER POLICY                     COMMIT PREPARED                  CREATE TABLE                     DROP INDEX                       EXECUTE                          SHOW
  ALTER PROCEDURE                  COPY                             CREATE TABLE AS                  DROP LANGUAGE                    EXPLAIN                          START TRANSACTION
  ALTER PUBLICATION                CREATE ACCESS METHOD             CREATE TABLESPACE                DROP MATERIALIZED VIEW           FETCH                            TABLE
  ALTER ROLE                       CREATE AGGREGATE                 CREATE TEXT SEARCH CONFIGURATION DROP OPERATOR                    GRANT                            TRUNCATE
  ALTER ROUTINE                    CREATE CAST                      CREATE TEXT SEARCH DICTIONARY    DROP OPERATOR CLASS              IMPORT FOREIGN SCHEMA            UNLISTEN
  ALTER RULE                       CREATE COLLATION                 CREATE TEXT SEARCH PARSER        DROP OPERATOR FAMILY             INSERT                           UPDATE
  ALTER SCHEMA                     CREATE CONVERSION                CREATE TEXT SEARCH TEMPLATE      DROP OWNED                       LISTEN                           VACUUM
  ALTER SEQUENCE                   CREATE DATABASE                  CREATE TRANSFORM                 DROP POLICY                      LOAD                             VALUES
  ALTER SERVER                     CREATE DOMAIN                    CREATE TRIGGER                   DROP PROCEDURE                   LOCK                             WITH
  ALTER STATISTICS                 CREATE EVENT TRIGGER             CREATE TYPE                      DROP PUBLICATION                 MOVE
  ALTER SUBSCRIPTION               CREATE EXTENSION                 CREATE USER                      DROP ROLE                        NOTIFY

查看数据库列表

postgres-# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

查看所有用户角色

postgres-# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

查看当前的用户角色

postgres=# \c
You are now connected to database "postgres" as user "postgres".

要退出 psql 命令行

postgres=# \q

2.2 创建数据库testdb

创建一个用户数据库 testdb。

# 创建数据库
postgres=# create database testdb;
CREATE DATABASE

# 查看所有数据库
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)

选择指定的数据库testdb。

# 选择数据库
postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".

2.3 创建新用户

通常来说,不建议直接使用超级管理员账户来直接进行数据操作,我们可以创建一些用户来完成对数据库的使用,避免超级管理员权限过大的问题。

创建2个新用户 test 和 test_readonly,密码都为 test 。test用户可直接完全访问testdb数据库,test_readonly用户仅直接只读访问testdb数据库。


postgres=# create user test with password 'test';
CREATE ROLE
postgres=# create user test_readonly with password 'test';
CREATE ROLE

给 test 用户授权可以完成控制 testdb 。


postgres=# GRANT ALL PRIVILEGES ON DATABASE testdb TO test;
GRANT

给 test_readonly 用户授权可以只读访问 testdb 。


# 切换数据库
postgres=# \c testdb;
You are now connected to database "testdb" as user "postgres".

# 赋予只读权限
testdb=# GRANT SELECT ON ALL TABLES in schema public TO test_readonly ;
GRANT

2.4 建表
让我们切换一下用户,使用test用户,建表和插入数据。


testdb=# \c - test
You are now connected to database "testdb" as user "test".

在testdb数据库中,建表,主键自增


# 建表
testdb=# create table account(
testdb(#     user_id serial primary key,
testdb(#     username varchar(50) unique not null,
testdb(#     password varchar(50) not null
testdb(# );
CREATE TABLE

查看testdb库的所有表


testdb=> \d
                List of relations
 Schema |        Name         |   Type   | Owner
--------+---------------------+----------+-------
 public | account             | table    | test
 public | account_user_id_seq | sequence | test
(2 rows)

查看account表的数据结构


# 查看表结构
testdb=> \d  account
                                       Table "public.account"
  Column  |         Type          | Collation | Nullable |                 Default
----------+-----------------------+-----------+----------+------------------------------------------
 user_id  | integer               |           | not null | nextval('account_user_id_seq'::regclass)
 username | character varying(50) |           | not null |
 password | character varying(50) |           | not null |
Indexes:
    "account_pkey" PRIMARY KEY, btree (user_id)
    "account_username_key" UNIQUE CONSTRAINT, btree (username)

2.5 插入数据

插入3条数据,因为主键是自增的,所以我们不用插入主键


# 单条插入
testdb=> INSERT INTO account(username , password ) VALUES('a1', 'a1');
INSERT 0 1

# 多条插入
testdb=> INSERT INTO account(username , password ) VALUES('a2', 'a2'),('a3', 'a3');
INSERT 0 2

进行查询,共有3条记录。


testdb=> SELECT * FROM account;
 user_id | username | password
---------+----------+----------
       1 | a1       | a1
       2 | a2       | a2
       3 | a3       | a3
(3 rows)

2.5 修改数据
修改user_id = 1的数据,username 改为 aaa1


# 修改数据
testdb=> UPDATE account SET username = 'aaa1' where user_id = 1;
UPDATE 1

# 进行查询
testdb=>  SELECT * FROM account;
 user_id | username | password
---------+----------+----------
       2 | a2       | a2
       3 | a3       | a3
       1 | aaa1     | a1
(3 rows)

修改user_id = 2的数据


# 删除数据
testdb=> DELETE FROM account where user_id = 2;
DELETE 1

# 进行查询
testdb=> SELECT * FROM account;
 user_id | username | password
---------+----------+----------
       3 | a3       | a3
       1 | aaa1     | a1
(2 rows)

2.6 切换只读用户test_readonly

切换用户test_readonly


testdb=# \c - test_readonly;
You are now connected to database "testdb" as user "test_readonly".

# 进行查询
testdb=> SELECT * FROM account;
 user_id | username | password
---------+----------+----------
       3 | a3       | a3
       1 | aaa1     | a1
(2 rows)

进行插入、修改、删除操作。


# 插入拒绝
testdb=> INSERT INTO account(username , password ) VALUES('a4', 'a4');
ERROR:  permission denied for table account

# 修改拒绝
testdb=> UPDATE account SET username = 'aaa3' where user_id = 3;
ERROR:  permission denied for table account

# 删除拒绝
testdb=> DELETE FROM account where user_id = 1;
ERROR:  permission denied for table account

3. 用pgAdmin远程访问数据库

安装好PostgreSQL数据库后,我们就需要进行数据库访问操作了,通常会使用远程的客户端程序来执行SQL语句,使用官方的pgAdmin客户端软件。

3.1 pgadmin下载

我们使用官方的pgadmin 4的客户端来进行数据库操作,pgadmin 4的客户端下载地址为:https://www.pgadmin.org/download/

下载后,我在window 10上进行安装,打开是web版本的界面。

添加数据库服务器,并建立连接,发现数据库连接被拒绝。

默认情况下,postgreSQL数据库只允许本机连接,要在其他机器上远程连接到postgreSQL数据库还需要修改相应的配置。

3.2 修改远程连接配置

前方有坑!按照网上的大部分教程,我们要修改配置文件,/etc/postgresql/12/main/postgresql.conf , 找到”#listen_addresses = ‘localhost'”中的注释去掉并改为 listen_addresses = ‘*’,允许远程客户端连接

~ sudo vi /etc/postgresql/12/main/postgresql.conf

listen_addresses = '*'

PostgreSQL客户端身份验证在名为的配置文件中定义pg_hba.conf。缺省情况下,PostgreSQL使用对等身份验证方法进行本地连接。

接下来,打开允许访问的IP段,修改 /etc/postgresql/12/main/pg_hba.conf , 在文件末尾添加 host all all 0.0.0.0 0.0.0.0 md5 ,表示允许任何IP连接

~ sudo vi /etc/postgresql/12/main/pg_hba.conf

host all all 0.0.0.0 0.0.0.0 md5

最后,重启数据库,就生效了。

sudo /etc/init.d/postgresql restart

我们检查postgresql 的网络监听端口

~ netstat -nlt|grep 5432
  tcp        0      127.0.0.1:5432            0.0.0.0:*               LISTEN

怎么一直都是127.0.0.1:5432而没有编程0 0.0.0.0:5432。我确认过了好几次呢。

问题三:远程配置怎么不生效?
仔细研究后发现,我们前文中在 新创建了 /usr/local/pgsql/data 数据空间,initdb的过程,会把pg_hba.conf和postgresql.conf文件初始化,所以需要改动 /usr/local/pgsql/data 目录下的这2个配置文件才可以。

进入目录 /usr/local/pgsql/data,发现又没有权限。

~ cd /usr/local/pgsql/data
-bash: cd: /usr/local/pgsql/data: Permission denied

切换为postgres用户,再进入目录 /usr/local/pgsql/data

# 切换用户
~ su postgres
Password:

# 进入目录 
~  cd /usr/local/pgsql/data
 ls
base          pg_hba.conf    pg_notify     pg_stat      pg_twophase  postgresql.auto.conf
global        pg_ident.conf  pg_replslot   pg_stat_tmp  PG_VERSION   postgresql.conf
pg_commit_ts  pg_logical     pg_serial     pg_subtrans  pg_wal       postmaster.opts
pg_dynshmem   pg_multixact   pg_snapshots  pg_tblspc    pg_xact      postmaster.pid

修改pg_hba.conf,在最后一行加入host all all 0.0.0.0 0.0.0.0 md5。修改 postgresql.conf 文件,把 listen_addresses = ‘*’。

退出postgress用户

postgres@conan:/usr/local/pgsql/data$ exit
exit
conan@conan:/var/lib/postgresql

再用pg_ctl命令进行重启数据库服务。


# 停止数据库
~ sudo -u postgres /usr/lib/postgresql/12/bin/pg_ctl -D /usr/local/pgsql/data -l logfile stop
[sudo] password for conan:
waiting for server to shut down.... done
server stopped

# 启动数据库
~ sudo -u postgres /usr/lib/postgresql/12/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
waiting for server to start.... done
server started

查看服务器端口

# 检查PostgreSQL服务器占用端口
~ netstat -nlt|grep 5432
  tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN

终于,从之前的网络监听从 127.0.0.1:5432 变成 0 0.0.0.0:5432,表示PostgreSQL已经允许远程登陆访问。

3.3 使用pgadmin进行远程连接

使用pgadmin配置数据库的远程连接。

进入pgadmin的界面,功能很强大,查看testdb的数据库统计。

进行数据表的SQL查询。

通过上面的操作,我们就把PostgreSQL数据库服务器,在Linux Ubuntu中的系统安装完成,并完成了数据库的客户端远程连接访问!希望本文能让所有的PostgreSQL新手快速上手,顺利完成PostgreSQL数据库的初探。

转载请注明出处:
http://blog.fens.me/linux-postgresql-install/

打赏作者

DP-900认证考试攻略

架构师的信仰系列文章,主要介绍我对系统架构的理解,从我的视角描述各种软件应用系统的架构设计思想和实现思路。

从程序员开始,到架构师一路走来,经历过太多的系统和应用。做过手机游戏,写过编程工具;做过大型Web应用系统,写过公司内部CRM;做过SOA的系统集成,写过基于Hadoop的大数据工具;做过外包,做过电商,做过团购,做过支付,做过SNS,也做过移动SNS。以前只用Java,然后学了PHP,现在用R和Javascript。最后跳出IT圈,进入金融圈,研发量化交易软件。

架构设计就是定义一套完整的程序规范,坚持架构师的信仰,做自己想做的东西。

关于作者:

  • 张丹,分析师/程序员/Quant: R,Java,Nodejs
  • blog: http://fens.me
  • email: bsspirit@gmail.com

转载请注明出处:
http://blog.fens.me/certificate-dp900/

前言

当前微软基于Azure云服务有3个基础认证,分别是AZ-900,AI-900和DP-900。AZ-900是对于Azure云计算的产品和框架的学习,AI-900是对于Azure上认识服务(AI服务)的产品和框架的学习,DP-900则是对于Azure上数据库的产品和框架的学习。掌握了这3个方向知识,对于Azure上的云服务就能吃透了。不得不说,微软Azure的产品体系真是强大。

关于AZ-900和AI-900的考试详细介绍,请参考文章 AZ-900认证考试攻略AI-900认证考试攻略

目录

  1. 考试契机
  2. 学习路径
  3. 复习准备

1. 考试契机

考过了AZ-900和AI-900后,突然认识到了,产品的规划如此重要。之前,一直在穷尽脑汁,设计自己公司的产品。当看到远方“巨人”已经早已设计好了数据产品的路线,果断放弃闭门造车的想法,向行业的领头者学习。只有具备了相同的视野,才有自己发展的机会。所以,DP-900的契机,是自我认知的一个冲动,跟上巨人的脚步。

DP-900的认证目标,掌握云环境中数据处理和数据库使用的基本知识,区别关系型数据库与非关系型数据库数据结构的区别和对应该产品。

DP-900的工作角色包括DBA,数据工程师和数据分析师。DP-900关于数据基础知识,也可用于准备其他基于Azure角色的认证,如Azure数据工程师助理[DP-200 / DP-201],Azure数据科学家助理[DP-100]和Azure数据库管理员助理[DP-300] ,但这不是任何这些考试的先决条件。

微软认证体系:AZ-900,AI-900和DP-900是Azure基础级认证,它们构成了助理和专家级认证的基础,可以帮助我们建立知识并熟悉Azure的工作环境。

考试完成后,会获得一份考试能力评估表,来告诉你哪部分做的好,哪部分做的不好。我考试的能力评估表:我考了882分(700通过),总体来说还是不错的成绩。

然后,就获得的证书电子版的证书 Microsoft Certified: Azure Data Fundamentals。

最后,可以linkedin上面自动同步证书,我的linkedin:https://www.linkedin.com/in/bsspirit/

2. 学习路径

Microsoft Certified: Microsoft Azure Data Fundamentals ,DP-900认证考试是Azure Data 的数据平台的认证,包括数据库设计,关系型数据,非关系型数据,数据仓库的多种数据基础知识,主要涉及4大部分内容。

  • Azure 数据基础知识探究核心数据概念 (15-20%)
  • 了解 Azure 中的关系数据(25-30%)
  • 了解 Azure 中的非关系数据 (25-30%)
  • 了解 Azure 中的新式数据仓库分析 (25-30%)

微软docs上,给出了DP-900完整的学习路径,https://docs.microsoft.com/zh-cn/learn/certifications/exams/dp-900

学习大纲:
第一部分:Azure 数据基础知识探究核心数据概念
了解云环境中数据库概念的基础知识,掌握云数据服务的基本技能,并建构 Microsoft Azure 中云数据服务的基础知识。 你将确定并描述核心数据概念(如关系、非关系、大数据和分析),并探索如何通过 Microsoft Azure 实现该技术。 你将探究数据世界中的角色、任务和责任。

  • 1.1 探究核心数据概念
  • 1.2 了解数据世界中的角色和职责
  • 1.3 描述关系数据的概念
  • 1.4 探索非关系数据的概念
  • 1.5 探索数据分析的概念

第二部分:了解 Azure 中的关系数据
了解云环境中数据库概念的基础知识,掌握云数据服务的基本技能,并建构 Microsoft Azure 中云数据服务的基础知识。 你将了解关系数据产品/服务,通过 Microsoft Azure 的云数据解决方案预配和部署关系数据库并查询关系数据。

  • 2.1 了解 Azure 中的关系数据产品/服务
  • 2.2 了解如何在 Azure 中预配和部署关系数据库产品/服务
  • 2.3 在 Azure 中查询关系数据

第三部分:了解 Azure 中的非关系数据
了解云环境中数据库概念的基础知识,掌握云数据服务的基本技能,并建构 Microsoft Azure 中云数据服务的基础知识。 你将了解非关系数据产品/服务,通过 Microsoft Azure 预配和部署非关系数据库以及非关系数据存储。

  • 3.1 了解 Azure 中的非关系数据产品/服务
  • 3.2 了解如何在 Azure 中预配和部署非关系数据服务
  • 3.3 管理 Azure 中的非关系数据存储

第四部分:了解 Azure 中的新式数据仓库分析
了解云环境中数据库概念的基础知识,掌握云数据服务的基本技能,并建构 Microsoft Azure 中云数据服务的基础知识。 你将了解 Azure 中用于构建数据分析解决方案的处理选项。 你将探索 Azure Synapse Analytics、Azure Databricks 和 Azure HDInsight。

  • 4.1 检查新式数据仓库的组件
  • 4.2 探索 Azure 中的数据引入
  • 4.3 了解 Azure 中的数据存储和处理
  • 4.4 开始使用Power BI进行构建

学习的内容很有意思,与我目前的工作有大量的交集。我们在做数据分析时,会接触到各种不同的数据源,有些是结构化的业务数据,有些是非结构化的文字或地图,有些表数据量巨大需要离些计算,模型训练完成后,又需要与作业系统对接,实时在线处理等。通过本次的学习,一方面可以加强概念理解,另一方面也可以有机会尝试用利用微软的产品,来完成复杂的机器学习的任务。

3. 复习准备

虽然,微软docs已经有了完整的学习大纲,真学起来还是要花点时间的,重点就在于名词解释和在Azure的不同数据库的不同特性。

3.1 名字解释
下面开始,名词解释:

Azure上的数据存储

  • Azure File Storage, 是一个通过 Server Message Block (SMB) 协议提供云端文件共享的服务。通过 File Storage 共享的文件能够被加载为云端或本地主机的磁盘,应用程序可以通过文件 API 像访问本地文件一样访问这些文件。
  • Azure Disk Storage,Azure 托管磁盘是由 Azure 托管并与 Azure 虚拟机配合使用的块级存储卷。托管磁盘类似于本地服务器中的物理磁盘,但它是虚拟化的。使用托管磁盘时,只需指定磁盘大小、磁盘类型并预配磁盘即可。预配此磁盘后,Azure 会处理剩余磁盘。
  • Azure Blog Storage,Azure Blob 存储是 Microsoft 提供的适用于云的对象存储解决方案。Blob 存储最适合存储巨量的非结构化数据。非结构化数据是不遵循特定数据模型或定义的数据(如文本或二进制数据)。
  • Azure Data Lake Storage, 数据湖是用于存储大量原始数据的存储库。 由于数据原始且未经处理,因此其加载和更新速度非常快,但数据并未采用适合高效分析的结构。 可以将数据湖看作是在引入数据进行修改并转换为适合执行分析的格式前的暂存点。 Azure Data Lake Storage 本质上是 Azure Blob 存储的扩展,组织为近乎无限大的文件系统,采用传统文件系统的分层目录结构和文件系统语义,同时具备 Azure 提供的安全性和可伸缩性。
  • Archive,存档,Azure存储提供了不同的访问层,允许以最具成本效益的方式存储 Blob 对象数据。可用的访问层包括::适用于存储经常访问的数据。:适用于存储不常访问且存储时间至少为 30 天的数据。存档:适用于存储极少访问、存储时间至少为 180 天且延迟要求(以小时计)不严格的数据。

Azure Data Factory,数据工厂被描述为一项数据集成服务。 Azure 数据工厂的用途是从一个或多个数据源中检索数据,并将其转换为你可处理的格式。 数据源可能以不同的方式呈现数据,并且包含需要筛选掉的干扰词。Azure 数据工厂使你可以提取感兴趣的数据,并放弃其余数据。

Azure 数据工厂由以下关键组件组成,这些组件组合起来提供一个平台,供你在上面编写数据驱动型工作流。

  • 管道:数据工厂可以包含一个或多个管道。 管道是执行任务单元的活动的逻辑分组。 管道中的活动可以共同执行一项任务。 例如,一个管道可能包含一组活动,这些活动从 Azure Blob 引入数据,然后在 HDInsight 群集上运行 Hive 查询,以便对数据分区。这样做的好处是,可以通过管道以集的形式管理活动,不必对每个活动单独进行管理。 管道中的活动可以链接在一起来按顺序执行,也可以独立并行执行。
  • 活动:活动表示管道中的处理步骤。 例如,可以使用复制活动将数据从一个数据存储复制到另一个数据存储。 同样,可以使用在 Azure HDInsight 群集上运行 Hive 查询的 Hive 活动来转换或分析数据。 数据工厂支持三种类型的活动:数据移动活动、数据转换活动 和 控制活动。
  • 数据集:数据集代表数据存储中的数据结构,这些结构直接指向需要在活动中使用的数据,或者将其作为输入或输出引用。
  • 链接服务:链接服务十分类似于连接字符串,用于定义数据工厂连接到外部资源时所需的连接信息。 链接服务定义到数据源的连接,而数据集则代表数据的结构。链接服务包括,创建链接服务,数据存储链接服务和计算链接服务。
  • 集成运行时:集成运行时 (IR) 是 Azure 数据工厂用于在不同的网络环境之间提供以下数据集成功能的计算基础结构:包括 数据移动,活动分派 和 SSIS包执行。数据移动:跨公用网络中的数据存储和专用网络中的数据存储复制数据。 它提供对内置连接器、格式转换、列映射以及性能和可扩展数据传输的支持。活动分派:分派和监视在各种计算服务(如 Azure HDInsight、Azure SQL 数据库、SQL Server 等等)上运行的转换活动。SSIS 包执行:在托管的 Azure 计算环境中本机执行 SQL Server 集成服务 (SSIS) 包。

数据仓库:数据仓库是来自一个或多个不同源的集成数据的集中存储库。数据仓库存储当前和历史数据,并用于报告和分析数据。为了将数据移入数据仓库,需要从包含重要业务信息的各种来源中定期提取数据。随着数据的移动,可以对其进行格式化,清理,验证,汇总和重组。


Azure上的数据工具:

  • Azure Databrick, 是在 Azure 上运行的 Apache Spark 环境,可提供大数据处理、流式传输和机器学习功能。 Apache Spark 是一个高效的数据处理引擎,可以非常快速地使用和处理大量数据。 有许多 Spark 库,可用于执行 SQL 处理、聚合等任务,还可以用于基于你的数据生成和训练机器学习模型。Azure Databricks 还支持结构化流处理。 在此模型中,Databricks 以增量方式执行计算,并在流数据到达时持续更新结果。
  • Azure Synapse Analytics, 是一个分析引擎,类似于Hadoop 它旨在极快速地处理大量数据,Azure Synapse Analytics 利用大规模并行处理 (MPP) 体系结构。使用 Synapse Analytics,你可以从外部源(如平面文件、Azure Data Lake 或其他数据库管理系统)引入数据,然后将此数据转换并聚合为适合分析处理的格式。 你可以对此数据执行复杂的查询并生成报表、图和图表。Azure Synapse Analytics 支持两种计算模型:SQL 池和 Spark 池。
  • Azure Synapse Link 是一种云原生混合事务和分析处理 (HTAP) 功能,使用该功能可以对 Azure Cosmos DB 中存储的操作数据进行准实时分析。
  • Synapse Studio,是一个 Web 界面,使用该界面可以以交互方式创建池和管道。 使用 Synapse Studio 可以开发、测试和调试 Spark 笔记本和 Transact-SQL 作业。
  • PolyBase,用于导入外部数据源数据,如Hadoop , Azure Blob, SQL Server、Oracle、Teradata 和 MongoDB 中的外部数据, 类似于sqope。
  • Azure HDInsight,是一项大数据处理服务,它在 Azure 环境中提供使用 Spark 等技术的平台。 HDInsight 实现了在一组计算机之间分发处理的群集模型。 此模型与 Synapse Analytics 所使用的模型相似,不同之处在于节点运行的是 Spark 处理引擎而不是 Azure SQL 数据库。

大数据架构,大数据体系结构旨在处理对于传统数据库系统而言太大或太复杂的数据的提取,处理和分析。大数据解决方案通常涉及以下一种或多种类型的工作负载:静态处理大数据源,实时处理运动中的大数据,交互式探索大数据,预测分析和机器学习。

  • 批量处理,常见的大数据场景是静态数据的批处理。源数据可以通过源应用程序本身或业务流程来加载到数据存储中。然后,数据由并行作业就地处理,该作业也可以由业务流程启动。在将转换后的结果加载到分析数据存储中之前,该处理可以包括多个迭代步骤,可以由分析和报告组件进行查询。
  • 实时处理,实时处理处理实时捕获的数据流,并以最小的延迟对其进行处理,以生成实时(或近实时)报告或自动响应。例如,实时流量监控解决方案可能使用传感器数据来检测高流量。该数据可用于动态更新地图以显示拥堵,或自动启动高占用车道或其他交通管理系统。

ETL过程,提取,转换和加载。一个普遍问题是如何从多种来源以多种格式收集数据,并将其移至一个或多个数据存储中。目标可能不是与源相同的数据存储类型,并且通常格式不同,或者在将数据加载到最终目标之前需要对数据进行整形或清除。

  • 提取(Extract):数据抽取是指把操作性数据(ODS)源数据抽取到数据仓库(DW)中,然后处理成展示给相关人员查看的数据。
  • 转换(Transform):就是把不需要的和不符合规范的数据进行处理。数据清洗最好不要放在抽取的环节进行,考虑到有时可能会查原始数据。
  • 加载(Load):数据拉取,清洗完之后,就需要展示了。一般是把清洗好的数据加载到数据库中,然后在各系统中使用,或者使用BI直接给相关人员展示

两种数据处理系统:OLAP和OLTP:

  • OLAP:在线分析处理,是一种组织大型企业数据库并支持复杂分析的技术。它可以用于执行复杂的分析查询,而不会负面影响交易系统。OLAP对于在大量数据上应用聚合计算特别有用。OLAP允许用户将多维数据划分为多个切片,这些切片可以在二维中查看(例如数据透视表),也可以按特定值过滤数据。
  • OLTP:在线交易处理,使用计算机系统对交易数据的管理称为在线交易处理。OLTP系统记录在组织的日常运营中发生的业务交互,并支持查询此数据以进行推断。交易通常需要是原子的一致的。原子性意味着整个事务始终作为一个工作单元成功或失败,并且永远不会处于半完成状态。如果事务无法完成,则数据库系统必须回滚作为该事务一部分已经完成的所有步骤。

Gremlin,实现针对 Cosmos DB 的图形数据库接口,图形是数据对象和定向关系的集合。

RU/s,Request Units,请求单位,视为满足对数据库的简单读取请求所需的计算量和 I/O 资源量。 Microsoft 为读取一个包含 10 个字段的 1 KB 文档所需的资源提供大约 1 RU 的度量值。 因此,每秒 1 RU 的吞吐量 (RU/s) 将支持每秒读取单个 1 KB 文档的应用程序。如果你将免费层折扣应用于你的 Cosmos DB 帐户,你将免费获取单个数据库或容器的前 400 RU/s。 对于大多数中小型数据库而言,400 RU/s 的容量已足够。

Azure SQL数据库和Azure SQL托管实例:Azure SQL数据库和SQL托管实例与SQL Server的最新稳定版本共享一个公共代码库。大多数标准SQL语言,查询处理和数据库管理功能都是相同的。注意:Azure SQL托管实例支持,本地的跨数据库事物。

Transparent Data Encryption(TDE),透明数据加密,这种加密称为静态数据加密,即数据和日志文件。为了帮助保护数据库,可以采取以下预防措施:设计一个安全的系统,加密机密资产,在数据库服务器周围建立防火墙。

SQL Server Data Tools(SSDT),面向项目的离线数据库开发工具,用于提供的用于创作,构建,调试和发布数据库项目的功能。可从 Visual Studio 中的“工具”菜单获得。 若要连接到现有的 Azure SQL 数据库实例

Azure Data Studio,数据库管理工具,是一种图形实用工具,用于从桌面创建和运行 SQL 查询,可以连接到本地 SQL Server 数据库、Azure SQL 数据库、PostgreSQL、Azure SQL 数据仓库和 SQL Server 大数据群集。

SQL Server Management Studio(SSMS), 是一种集成环境,用于管理从 SQL Server 到 Azure SQL 数据库的任何 SQL 基础结构。 SSMS 提供用于配置、监视和管理 SQL Server 和数据库实例的工具。 使用 SSMS 部署、监视和升级应用程序使用的数据层组件,以及生成查询和脚本。

Power BI 是一系列的软件服务、应用和连接器,这些软件服务、应用和连接器协同工作,将不相关的数据源转化为合乎逻辑、视觉上逼真的交互式见解。 不管你的数据是简单的 Microsoft Excel 工作簿,还是基于云的数据仓库和本地混合数据仓库的集合,Power BI 都可让你轻松连接到数据源,可视化(或发现)重要信息,并与所需的任何人共享这些信息。Power BI 中的基本构建基块:可视化效果,数据集,报表,仪表板,磁贴.

  • Dashboard,仪表盘,是通过可视化效果讲述故事的单个页面,常被称为画布。因为它被限制为一页,设计精良的仪表板仅包含该故事的亮点。读者可查看相关报表了解详细信息。
  • Drill Down,钻取,当视觉具有层次结构时,您可以向下钻取以显示其他详细信息。

关系型数据库的SQL语言:

  • 数据操作语言 (DML), 可以使用 DML 语句来处理关系表中的行。 利用这些语句,你可以检索(查询)数据、插入新行或编辑现有行。 如果不再需要行,也可以删除它们。SELECT 从表中选择/读取行,INSERT 向表中插入新行,UPDATE 编辑/更新现有行,DELETE 删除表中的现有行。
  • 数据定义语言 (DDL),使用 DDL 语句创建、修改和删除数据库中的表和其他对象(表、存储过程和视图等)。CREATE 在数据库中创建新对象,例如表或视图。ALTER 修改对象的结构。 例如,更改表以添加新列。DROP 从数据库中删除对象。RENAME 重命名现有对象。

关系数据库四个特性:原子性、一致性、隔离性和持久性(ACID)。

  • 原子性,确保将每个事务视为单个单位,要么完全成功要么完全失败。 如果构成事务的任何语句无法完成,整个事务都将失败,并且数据库保持不变。 原子系统必须保证每种情况下的原子性,包括电源故障、错误和崩溃。
  • 一致性,确保事务只能将数据库中的数据从一个有效状态转换为另一个有效状态。 一致的数据库绝不应丢失或以无法解释的方式创建数据。 在前面所述的银行转账示例中,如果将资金添加到某个帐户,则必须在某个地方有相应的资金扣除,或者如果资金是从外部收到的,则必须有描述资金来自何处的记录。 不能突然创建(或丢失)资金。
  • 隔离性,确保事务的并发执行使数据库处于与按顺序执行事务时相同的状态。 并发进程看不到处于不一致状态的数据(例如,已从一个帐户扣除了资金,但尚未存入到另一个帐户。)
  • 持久性,保证在提交事务后,即使发生系统故障(如停电或崩溃),它仍能保持已提交的状态。

Azure Cosmos DB的一致性级别

  • 强一致性:非常一致性提供可线性化保证。 可线性化是指并发处理请求。 保证读取操作返回项的最新提交版本。 客户端永远不会看到未提交或不完整的写入。 始终保证用户读取最新确认的写入。
  • 受限停滞一致性:保证读取操作遵循一致性前缀保证。 读取操作可以滞后于写入操作最多“K”个项版本(即“更新”)或“T”时间间隔,以先达到者为准。 换言之,如果选择有限过期,则可以通过两种方式配置“过期”
  • 会话一致性:在单个客户端会话中,将保证读取操作遵循一致前缀、单调读取、单调写入、读取写入和读取后写入保证。 这采用单个“写入器”会话,或者多个写入器共享会话令牌。
  • 一致前缀:返回的更新包含所有更新的一些前缀,不带间隔。 一致前缀一致性级别保证读取操作永远不会看到无序写入。
  • 最终一致性:不保证读取的顺序。 如果缺少任何进一步的写入,则副本最终会收敛。最终一致性是最弱的一致性形式,因为客户端可能会读取比之前读取的值还要旧的值。 最终一致性非常适合不需要任何顺序保证的应用程序。

3.2 模拟考试

整理完上面的名词解释,准备工作就算到位了,最后就是找份模拟题练练手。

我发现的免费模拟题只有CertBolt网站上提供了,可以用 CertBolt Microsoft DP-900 模拟题库,共有55道题目,练习一下也就够用了。我也在网上各种找了一些模拟题,整理成一个DP900模拟题PDF文件,给大家练手用。

毕竟是初级认证,考试都是基础知识,按照微软的文档进行复习准备,大概率都是可以通过的,本文的目的也是帮助大家准备考试,做到心中有底。

3.3 现场考试

最后,就是按照约好的时间,去考试中心完成考试,要带2种证明身份的证件。进到考场后,包要存起来,不能带任何东西,不能吃东西,不能喝水,不能与其他人说话等等,可以提前交卷。交卷后,找工作人员,拿到考试评估单,就可以回家庆祝了。与AZ-900的现场考试流程一样,大家可以参考文章AZ-900认证考试攻略

本文主要是记录一下考试的前后经历,对于IT的小伙伴们,考个认证系统地学习知识,还是很有用的。未来的成就,都是之前的积累,爆发就在未来的某个瞬间。祝大家考试顺利!

转载请注明出处:
http://blog.fens.me/certificate-dp900/

打赏作者

在Ubuntu中安装HBase

R利剑NoSQL系列文章,主要介绍通过R语言连接使用nosql数据库。涉及的NoSQL产品,包括RedisMongoDBHBaseHiveCassandraNeo4j。希望通过我的介绍让广大的R语言爱好者,有更多的开发选择,做出更多地激动人心的应用。

关于作者:

  • 张丹(Conan), 程序员Java,R,PHP,Javascript
  • weibo:@Conan_Z
  • blog: http://blog.fens.me
  • email: bsspirit@gmail.com

转载请注明出处:
http://blog.fens.me/linux-hbase-install/

linux-hbase

前言

HBase是Hadoop家族中的一个分布式数据库产品,HBase支持高并发读写,列式数据存储,高效的索引,自动分片,自动Region迁移等许多优点,已经越来越多的被界业认可并实施。

目录

  1. 在Ubuntu中环境准备
  2. HBase安装
  3. Thrift安装

1 在Ubuntu中环境准备

HBase是基于Java开发的运行Hadoop平台上分布式NoSQL数据库软件,HBase没有提供Windows系统安装版本。我在这里也只介绍HBase在Linux Ubuntu系统中的安装。

由于HBase是运行在Hadoop平台上面的,因此我们需要先安装好Hadoop的环境,Hadoop的安装请参考文章:[Hadoop历史版本安装](http://blog.fens.me/hadoop-history-source-install/)

HBase没有提供apt的软件源安装,我们需要自己去官方网络下载HBase软件包进行安装。HBase下载页:http://www.apache.org/dyn/closer.cgi/hbase/

系统环境:

  • Linux Ubuntu 12.04.2 LTS 64bit server
  • Java JDK 1.6.0_45
  • Hadoop 1.1.2

2 HBase安装

2.1 下载HBase


# 通过wget命令下载
~ wget http://www.gaidso.com/apache/hbase/stable/hbase-0.94.18.tar.gz

# 解压HBase
~ tar xvf hbase-0.94.18.tar.gz

# 移动HBase目录到文件夹
~ mv hbase-0.94.18/ /home/conan/hadoop/

# 进入目录
~ cd /home/conan/hadoop/hbase-0.94.18

2.2 配置HBase

2.2.1 修改启动文件hbase-env.sh


~ vi conf/hbase-env.sh

#打开注释
export JAVA_HOME=/home/conan/toolkit/jdk16
export HBASE_CLASSPATH=/home/conan/hadoop/hadoop-1.1.2/conf
export HBASE_MANAGES_ZK=true

2.2.2 修改配置文件 hbase-site.xml


~ vi conf/hbase-site.xml

<configuration>
<property>
<name>hbase.rootdir</name>
<value>hdfs://master:9000/hbase</value>
</property>

<property>
<name>hbase.cluster.distributed</name>
<value>true</value>
</property>

<property>
<name>dfs.replication</name>
<value>1</value>
</property>

<property>
<name>hbase.zookeeper.quorum</name>
<value>master</value>
</property>

<property>
<name>hbase.zookeeper.property.clientPort</name>
<value>2181</value>
</property>

<property>
<name>hbase.zookeeper.property.dataDir</name>
<value>/home/conan/hadoop/hdata</value>
</property>
</configuration>

复制hadoop环境的配置文件和类库


~ cp ~/hadoop/hadoop-1.1.2/conf/hdfs-site.xml conf/
~ cp ~/hadoop/hadoop-1.1.2/hadoop-core-1.1.2.jar lib/
~ mkdir /home/conan/hadoop/hdata

2.3 启动hadoop和hbase


~ /home/conan/hadoop/hadoop-1.1.2/bin/start-all.sh
~ /home/conan/hadoop/hbase-0.94.18/bin/start-hbase.sh

# 查看hbase进程
~ jps
13838 TaskTracker
13541 JobTracker
15946 HMaster
16756 Jps
12851 NameNode
13450 SecondaryNameNode
13133 DataNode
15817 HQuorumPeer
16283 HRegionServer

2.4 打开HBase命令行客户端访问Hbase


~ bin/hbase shell
HBase Shell; enter 'help' for list of supported commands.
Type "exit" to leave the HBase Shell
Version 0.94.18, r1577788, Sat Mar 15 04:46:47 UTC 2014

hbase(main):002:0> help
HBase Shell, version 0.94.18, r1577788, Sat Mar 15 04:46:47 UTC 2014
Type 'help "COMMAND"', (e.g. 'help "get"' -- the quotes are necessary) for help on a specific command.
Commands are grouped. Type 'help "COMMAND_GROUP"', (e.g. 'help "general"') for help on a command group.

COMMAND GROUPS:
  Group name: general
  Commands: status, version, whoami

  Group name: ddl
  Commands: alter, alter_async, alter_status, create, describe, disable, disable_all, drop, drop_all, enable, enable_all, exists, is_disabled, is_enabled, list, show_filters

  Group name: dml
  Commands: count, delete, deleteall, get, get_counter, incr, put, scan, truncate

  Group name: tools
  Commands: assign, balance_switch, balancer, close_region, compact, flush, hlog_roll, major_compact, move, split, unassign, zk_dump

  Group name: replication
  Commands: add_peer, disable_peer, enable_peer, list_peers, list_replicated_tables, remove_peer, start_replication, stop_replication

  Group name: snapshot
  Commands: clone_snapshot, delete_snapshot, list_snapshots, restore_snapshot, snapshot

  Group name: security
  Commands: grant, revoke, user_permission

SHELL USAGE:
Quote all names in HBase Shell such as table and column names.  Commas delimit
command parameters.  Type  after entering a command to run it.
Dictionaries of configuration used in the creation and alteration of tables are
Ruby Hashes. They look like this:

  {'key1' => 'value1', 'key2' => 'value2', ...}

and are opened and closed with curley-braces.  Key/values are delimited by the
'=>' character combination.  Usually keys are predefined constants such as
NAME, VERSIONS, COMPRESSION, etc.  Constants do not need to be quoted.  Type
'Object.constants' to see a (messy) list of all constants in the environment.

If you are using binary keys or values and need to enter them in the shell, use
double-quote'd hexadecimal representation. For example:

  hbase> get 't1', "key\x03\x3f\xcd"
  hbase> get 't1', "key\003\023\011"
  hbase> put 't1', "test\xef\xff", 'f1:', "\x01\x33\x40"

The HBase shell is the (J)Ruby IRB with the above HBase-specific commands added.
For more on the HBase Shell, see http://hbase.apache.org/docs/current/book.html

2.5 HBase简单命令操作


#创建一个新表student
hbase(main):003:0> create 'student','info'
0 row(s) in 1.2680 seconds

#查看所有的表
hbase(main):004:0> list
TABLE
student
1 row(s) in 0.0330 seconds

#查看student的表结构
hbase(main):005:0> describe 'student'
DESCRIPTION                                                 ENABLED
 'student', {NAME => 'info', DATA_BLOCK_ENCODING => 'NONE', true
  BLOOMFILTER => 'NONE', REPLICATION_SCOPE => '0', VERSIONS
  => '3', COMPRESSION => 'NONE', MIN_VERSIONS => '0', TTL =
 > '2147483647', KEEP_DELETED_CELLS => 'false', BLOCKSIZE =
 > '65536', IN_MEMORY => 'false', ENCODE_ON_DISK => 'true',
  BLOCKCACHE => 'true'}
1 row(s) in 0.1100 seconds

#同student表中插入一条数据
hbase(main):007:0> put 'student','mary','info:age','19'
0 row(s) in 0.0490 seconds

#从student表中取出mary的数据
hbase(main):008:0> get 'student','mary'
COLUMN                   CELL
 info:age                timestamp=1396366643298, value=19
1 row(s) in 0.0190 seconds

#让student表失效
hbase(main):009:0> disable 'student'
0 row(s) in 1.2400 seconds

#列出所有表
hbase(main):010:0> list
TABLE
student
1 row(s) in 0.0310 seconds

#删除student表
hbase(main):013:0>  drop 'student'
0 row(s) in 1.1100 seconds

#列出所有表
hbase(main):014:0> list
TABLE
0 row(s) in 0.0400 seconds

3 Thrift安装

安装完成HBase后,我们还需要安装Thrift,因为其他语言调用HBase的时候,是通过Thrift连接的。

Thrift是需要本地编译的,官方没有提供二进制安装包,首先下载thrift-0.9.1,Thrift下载页:http://thrift.apache.org/download

3.1 下载thrift

下载Thrift有两种方式,直接下载源代码发行包,或者通过git下载源代码,请选择其中一种方式下载。

3.1.1 直接下载源代码发行包 thrift-0.9.1.tar.gz


~ wget http://apache.fayea.com/apache-mirror/thrift/0.9.1/thrift-0.9.1.tar.gz
~ tar xvf thrift-0.9.1.tar.gz
~ mv thrift-0.9.1/ /home/conan/hadoop/
~ cd /home/conan/hadoop/

注:后文中的各种错误,都是这个包引起的

3.1.2 通过git下载源代码


~ git clone https://git-wip-us.apache.org/repos/asf/thrift.git thrift-git
~ mv thrift-git/ /home/conan/hadoop/
~ cd /home/conan/hadoop/

为了避免各种出错,建议使用git下载源代码安装

3.2 通过thrift-0.9.1.tar.gz 发行包安装Thrift

Thrift是需要本地编译的,在Thrift解压目录输入./configure,会列Thrift在当前机器所支持的语言环境。

3.2.1 安装Thrift的依赖包


sudo apt-get install libboost-dev libboost-test-dev libboost-program-options-dev libevent-dev automake libtool flex bison pkg-config g++ libssl-dev

如果只是为了连接rhbase,默认配置就可以了。如果除了希望支持rhbase访问,还支持PHP,Python,C++等语言的访问,就需要在系统中,装一些额外的类库。大家可以根据自己的要求,安装对应的软件包并设置Thrift的编译参数。

生成配置脚本


~  ./configure

//省略部分日志输出

thrift 0.9.1

Building C++ Library ......... : yes
Building C (GLib) Library .... : no
Building Java Library ........ : yes
Building C# Library .......... : no
Building Python Library ...... : yes
Building Ruby Library ........ : no
Building Haskell Library ..... : no
Building Perl Library ........ : no
Building PHP Library ......... : no
Building Erlang Library ...... : no
Building Go Library .......... : no
Building D Library ........... : no

C++ Library:
   Build TZlibTransport ...... : yes
   Build TNonblockingServer .. : yes
   Build TQTcpServer (Qt) .... : no

Java Library:
   Using javac ............... : javac
   Using java ................ : java
   Using ant ................. : /home/conan/toolkit/ant184/bin/ant

Python Library:
   Using Python .............. : /usr/bin/python

If something is missing that you think should be present,
please skim the output of configure to find the missing
component.  Details are present in config.log.

我本机的已支持C++, Java与Thrift的通信。

3.2.2 增加Python语言的通信

虽然Python已被显示支持与Thrift但在后面编译过程中,还是缺少一些Python的库,我们需要再增加Python的依赖库

安装Python的依赖包


sudo apt-get install python-all python-all-dev python-all-dbg

3.2.3 增加PHP语言的通信

安装PHP的依赖包


sudo apt-get install php5-dev php5-cli phpunit

生成配置脚本


~  ./configure --enable-thrift_protocol

//省略部分日志输出

thrift 0.9.1

Building C++ Library ......... : yes
Building C (GLib) Library .... : no
Building Java Library ........ : yes
Building C# Library .......... : no
Building Python Library ...... : yes
Building Ruby Library ........ : no
Building Haskell Library ..... : no
Building Perl Library ........ : no
Building PHP Library ......... : yes
Building Erlang Library ...... : no
Building Go Library .......... : no
Building D Library ........... : no

C++ Library:
   Build TZlibTransport ...... : yes
   Build TNonblockingServer .. : yes
   Build TQTcpServer (Qt) .... : no

Java Library:
   Using javac ............... : javac
   Using java ................ : java
   Using ant ................. : /home/conan/toolkit/ant184/bin/ant

Python Library:
   Using Python .............. : /usr/bin/python

PHP Library:
   Using php-config .......... : /usr/bin/php-config

If something is missing that you think should be present,
please skim the output of configure to find the missing
component.  Details are present in config.log.

我们看到Thrift的配置中,增加了对PHP语言的支持。

3.2.4 编译和安装


# 编译Thrift
~ make

//省略部分日志

make[5]: 正在进入目录 `/home/conan/hadoop/thrift-0.9.1/lib/php/src/ext/thrift_protocol'
make[5]: *** 没有指明目标并且找不到 makefile。 停止。
make[5]:正在离开目录 `/home/conan/hadoop/thrift-0.9.1/lib/php/src/ext/thrift_protocol'
make[4]: *** [src/ext/thrift_protocol/modules/thrift_protocol.so] 错误 2
make[4]:正在离开目录 `/home/conan/hadoop/thrift-0.9.1/lib/php'
make[3]: *** [all-recursive] 错误 1
make[3]:正在离开目录 `/home/conan/hadoop/thrift-0.9.1/lib/php'
make[2]: *** [all-recursive] 错误 1
make[2]:正在离开目录 `/home/conan/hadoop/thrift-0.9.1/lib'
make[1]: *** [all-recursive] 错误 1
make[1]:正在离开目录 `/home/conan/hadoop/thrift-0.9.1'
make: *** [all] 错误 2

在make生成过程,出现PHP的编译错误。从Thrift的错误列表中,我们可以找到错误描述( https://issues.apache.org/jira/browse/THRIFT-2265 ),这是由于Thrift-0.9.1发行包,打包时缺少了PHP扩展文件造成的错误,并在Thrift-0.9.2版本中修复。

所以,我们如果还想继续使用Thrift-0.9.1版本,则不能支持PHP语言。


# 生成配置信息,不包括PHP模块
~ ./configure --without-php_extension

# 编译Thrift
~ make

编译过程中,又出现了C++编译错误。


Makefile:832: 警告:覆盖关于目标“gen-cpp/ThriftTest.cpp”的命令
Makefile:829: 警告:忽略关于目标“gen-cpp/ThriftTest.cpp”的旧命令
/bin/bash ../../libtool --tag=CXX   --mode=link g++ -Wall -g -O2 -L/usr/lib   -o libtestgencpp.la  ThriftTest_constants.lo ThriftTest_types.lo ../../lib/cpp/libthrift.la -lssl -lcrypto -lrt -lpthread
libtool: link: ar cru .libs/libtestgencpp.a .libs/ThriftTest_constants.o .libs/ThriftTest_types.o
ar: .libs/ThriftTest_constants.o: No such file or directory
make[3]: *** [libtestgencpp.la] 错误 1
make[3]:正在离开目录 `/home/conan/hadoop/thrift-0.9.1/test/cpp'
make[2]: *** [all-recursive] 错误 1
make[2]:正在离开目录 `/home/conan/hadoop/thrift-0.9.1/test'
make[1]: *** [all-recursive] 错误 1
make[1]:正在离开目录 `/home/conan/hadoop/thrift-0.9.1'
make: *** [all] 错误 2

对于上面的2个编译错误,我决定换成git源代码的版本重新操作。

3.2 通过git源代码安装Thrift

运行安装命令


# 进行thrift-git目录
~ cd /home/conan/hadoop/thrift-git

# 复制0.9.1标签到新分支thrift-0.9.1
~ git checkout -b thrift-0.9.1 0.9.1

# 产生配置脚本
~ ./bootstrap.sh

# 生成配置信息
~ ./configure

# 编译Thrift
~ make

# 安装Thrift
~ sudo make install

走了许多弯路,终于使用git源代码版本安装好了Thrift。

查看thrift版本


~ thrift -version
Thrift version 0.9.1

接下来,我们启动HBase的Thrift Server服务


# 启动HBase的Thrift服务
~ /home/conan/hadoop/hbase-0.94.18/bin/hbase-daemon.sh start thrift
starting thrift, logging to /home/conan/hadoop/hbase-0.94.18/bin/../logs/hbase-conan-thrift-master.out

# 检查系统进程
~ jps
13838 TaskTracker
13541 JobTracker
15946 HMaster
32120 Jps
12851 NameNode
13450 SecondaryNameNode
13133 DataNode
32001 ThriftServer
15817 HQuorumPeer
16283 HRegionServer

我们看到ThriftServer已被启动,后面我们就可以使用多种语言,通过Thrift来访问HBase了,这样就完成了HBase的安装。

转载请注明出处:
http://blog.fens.me/linux-hbase-install/

打赏作者