R语言作为BI中ETL的工具

R语言作为BI中ETL的工具

R语言作为BI中ETL的工具，增删改

R语言提供了强大的R_package与各种数据库进行数据交互。
外加其强大数据变换清洗函数，为ＥＴＬ提供一条方便快捷的道路。
RODBC
ROracal
RMysql
Rmongodb

    step1 新建连接con，并查看其信息

library(RODBC)
con<-odbcConnect("LI")
con  

RODBC Connection 1
Details:
  case=nochange
  DSN=LI
  UID=
  Trusted_Connection=Yes
  APP=RStudio
  WSID=LIYI-PC

    step2 引入数据集USArrests

data(USArrests)
head(USArrests)
           Murder Assault UrbanPop Rape
Alabama      13.2     236       58 21.2
Alaska       10.0     263       48 44.5
Arizona       8.1     294       80 31.0
Arkansas      8.8     190       50 19.5
California    9.0     276       91 40.6
Colorado      7.9     204       78 38.7

    step3 将USArrests保存到sqlserver

sqlSave(con, USArrests)

sqlSave(channel, dat, tablename = NULL, append = FALSE,
        rownames = TRUE, colnames = FALSE, verbose = FALSE,
        safer = TRUE, addPK = FALSE, typeInfo, varTypes,
        fast = TRUE, test = FALSE, nastring = NULL)
channel      数据库连接通道
dat              data frame.要存入的数据集
tablename  character 数据库中表名
index          character 索引列的名字
append       logical逻辑变量 ，是否数据集添加/写入已存在的表
rownames  logical 逻辑变量 or character字符串,如果是logical，表示是否把rowname这个字符串作为数据库表首列列名，如果是字符串，则将新字符串作为表首列列名
colnames  logical 逻辑变量 是否将数据集的列名保留作为表的首行 (谨慎更改，可能导致，列名变成数据第一行，各列的 数据类型全部变为varchar(255)
verbose   display statements as they are sent to the server?
safer     logical逻辑变量.如果真，生成一个新表，不在已存在的表后添加。如果假，强制删除已存在的同名表并新建，或者删除表中已存在的数据，覆盖写入
addPK     logical逻辑变量,是否将首列作为主键
typeInfo  list 数据框中数据类型。包括character ,double ,integer
varTypes  an optional named character vector giving the DBMSs datatypes to be used for some (or all) of the columns if a table is to be created.可选项涉及各列数据类型转换，因为数据库中的数据类型比R语言中的要多很多。
fast      logical. If false, write data a row at a time. If true, use a parametrized INSERT INTO or UPDATE query to write all the data in one operation. 逻辑变量，如果F，数据将一次一行地写入，如果为Ture，将用到变量插入INSERT INTO，或者UPDATE 将数据一次性写入
nastring     optional character string to be used for writing NAs to the database. 选择哪种字符串，将缺失项在数据库中填充

getSqlTypeInfo("Microsoft SQL Server")
$double
[1] "float"

$integer
[1] "int"

$character
[1] "varchar(255)"

$logical
[1] "varchar(5)"

sqlSave(con, USArrests,rownames = "city", addPK = T) # 原没有列名的rownames改名为city,并设置首列为主键key

sqlSave(con, USArrests,'USA2',rownames = "city", addPK = T,fast=T,test=T)  #注意此操作可能在数据库中create名为USA2的空表

Binding: 'city' DataType 12, ColSize 255
Binding: 'Murder' DataType 6, ColSize 53
Binding: 'Assault' DataType 4, ColSize 10
Binding: 'UrbanPop' DataType 4, ColSize 10
Binding: 'Rape' DataType 6, ColSize 53
Parameters:
no: 1: city Alabamano: 2: Murder 13.2no: 3: Assault 236no: 4: UrbanPop 58no: 5: Rape 21.2
no: 1: city Alaskano: 2: Murder 10no: 3: Assault 263no: 4: UrbanPop 48no: 5: Rape 44.5
no: 1: city Arizonano: 2: Murder 8.1no: 3: Assault 294no: 4: UrbanPop 80no: 5: Rape 31
no: 1: city Arkansasno: 2: Murder 8.8no: 3: Assault 190no: 4: UrbanPop 50no: 5: Rape 19.5
no: 1: city Californiano: 2: Murder 9no: 3: Assault 276no: 4: UrbanPop 91no: 5: Rape 40.6
no: 1: city Coloradono: 2: Murder 7.9no: 3: Assault 204no: 4: UrbanPop 78no: 5: Rape 38.7
no: 1: city Connecticutno: 2: Murder 3.3no: 3: Assault 110no: 4: UrbanPop 77no: 5: Rape 11.1
no: 1: city Delawareno: 2: Murder 5.9no: 3: Assault 238no: 4: UrbanPop 72no: 5: Rape 15.8
no: 1: city Floridano: 2: Murder 15.4no: 3: Assault 335no: 4: UrbanPop 80no: 5: Rape 31.9
no: 1: city Georgiano: 2: Murder 17.4no: 3: Assault 211no: 4: UrbanPop 60no: 5: Rape 25.8
no: 1: city Hawaiino: 2: Murder 5.3no: 3: Assault 46no: 4: UrbanPop 83no: 5: Rape 20.2
no: 1: city Idahono: 2: Murder 2.6no: 3: Assault 120no: 4: UrbanPop 54no: 5: Rape 14.2
no: 1: city Illinoisno: 2: Murder 10.4no: 3: Assault 249no: 4: UrbanPop 83no: 5: Rape 24
no: 1: city Indianano: 2: Murder 7.2no: 3: Assault 113no: 4: UrbanPop 65no: 5: Rape 21
no: 1: city Iowano: 2: Murder 2.2no: 3: Assault 56no: 4: UrbanPop 57no: 5: Rape 11.3
no: 1: city Kansasno: 2: Murder 6no: 3: Assault 115no: 4: UrbanPop 66no: 5: Rape 18
# 此处省略10000字

sqlColumns    Enquire about the column structure of tables on an ODBC database connection. 访问数据库表的结构

columnsenquire<-sqlColumns(con,'USA2')

str(columnsenquire)

str(columnsenquire)
'data.frame':   5 obs. of  29 variables:
 $ TABLE_CAT                           : chr  "master" "master" "master" "master" ...
 $ TABLE_SCHEM                 

转载于:https://www.cnblogs.com/amengduo/p/9587070.html