MySQL

MySQL下group_concat函数的SAS实现

MySQL下的字符串聚合函数group_concat是一个非常实用的字符串合并函数， sas下的宏实现，

%macro group_concat(input, output, id, string, length);

data &output (drop=string_old);
set &input (rename=(&string=string_old));
by &id;
retain &string;
length &string $ &length; * set large enough to accommodate the maximum number of records per ID;
if first.&id then &string = '';
brand = catx(',',trim(&string),string_old);
if last.&id then output;
run;

%mend group_concat;

%macro group_concat(input, output, id, string, length);

data &output (drop=string_old);

set &input (rename=(&string=string_old));

by &id;

retain &string;

length &string $ &length; * set large enough to accommodate the maximum number of records per ID;

if first.&id then &string = '';

brand = catx(',',trim(&string),string_old);

if last.&id then output;

run;

%mend group_concat;

基本表结构如下, 按字母排序的变量和属性列表 # 变量类型长度输出格式输入格式标签 2 brand 字符 40 $40. $40. brand 1 customer_id 字符 …

SparkR连接PostgreSQL数据库

成功安装了SparkR后，让我们来尝试下如何从PostgreSQL读取数据，

Sys.setenv(SPARK_HOME="/Users/steven/Applications/spark2")
.libPaths(c(file.path(Sys.getenv("SPARK_HOME"), "R", "lib"), .libPaths()))

library(SparkR)

d.pg="org.postgresql:postgresql:9.4.1209.jre7"
sc <- sparkR.session(master="local[2]",sparkPackages=c(d.pg))

url<-"jdbc:postgresql://localhost:5432/steven?user=postgres&password="
driver<-"org.postgresql.Driver"
df.pg <- read.jdbc(source="jdbc", url=url, tableName ="public.mtcars",driver=driver)

printSchema(df.pg)

collect(df.pg)
#createOrReplaceTempView(df.pg,"mtcars")
#sql("select * from mtcars")

sparkR.session.stop()

Sys.setenv(SPARK_HOME="/Users/steven/Applications/spark2")

.libPaths(c(file.path(Sys.getenv("SPARK_HOME"), "R", "lib"), .libPaths()))

library(SparkR)

d.pg="org.postgresql:postgresql:9.4.1209.jre7"

sc <- sparkR.session(master="local[2]",sparkPackages=c(d.pg))

url<-"jdbc:postgresql://localhost:5432/steven?user=postgres&password="

driver<-"org.postgresql.Driver"

df.pg <- read.jdbc(source="jdbc", url=url, tableName ="public.mtcars",driver=driver)

printSchema(df.pg)

collect(df.pg)

#createOrReplaceTempView(df.pg,"mtcars")

#sql("select * from mtcars")

sparkR.session.stop()

从代码可以看到，主要指定sparkPackages和read.jdbc命令，这段命令只要稍做修改，同样适用MySQL等支持JDBC连接的数据库。

将R的data.frame写入MySQL数据库

这里主要想纠正一个问题，就是关于含中文字符的data frame写入MySQL时，网上给的错误说法：不能用dbWriteTable函数！！基本思路就是判断变量（列名）和数据是否为utf8编码，如果不是则转为utf8 data.frame则被转为data.table，一则偷懒，二则速度快如果是csv，强烈推荐data.table的fread，快，无编码坑

#library(readxl, quietly=TRUE)
#df <- data.frame(read_excel("d:\\data.xlsx",col_names = TRUE),stringsAsFactors=FALSE)

#如果是文本文件或者CSV，建议使用data.table，好处是快，以及避免read_csv的各种编码坑

#connect MySQL
library(RMySQL)
con <- dbConnect(MySQL(),user="user", password="pwd",dbname="db"
, host="mysql.yourhost.com" ,port=3306)


library(stringi)
toutf8<-function(x)
{
  if (is.factor(x))
  {
    x<-as.character(x)
  }
  if(is.character(x)){
    if(!all( stri_enc_isutf8(x))){
      x<-stri_encode(x, "", "UTF-8")
    }
    Encoding(x)<-"utf8"
  }
  x
}

#if R in Windows, convert columns and data to UTF-8
names(df)<-toutf8(names(df))

#下面的代码是偷懒，针对data.frame没有写，有兴趣的可以自己动手，使用data.table的好处还是快
library(data.table)
df<-as.data.table(df)

#if R in Windows, convert columns and data to UTF-8
if(Sys.info()[["sysname"]]=="Windows"){
  names(df)<-toutf8(names(df))
  df<-df[ , lapply(.SD, toutf8)]
  dbGetQuery(con, "set NAMES utf8")
}

dbWriteTable(con, "data",df)

#library(readxl, quietly=TRUE)

#df <- data.frame(read_excel("d:\\data.xlsx",col_names = TRUE),stringsAsFactors=FALSE)

#如果是文本文件或者CSV，建议使用data.table，好处是快，以及避免read_csv的各种编码坑

#connect MySQL

library(RMySQL)

con <- dbConnect(MySQL(),user="user", password="pwd",dbname="db"

, host="mysql.yourhost.com" ,port=3306)

library(stringi)

toutf8<-function(x)

{

if (is.factor(x))

{

x<-as.character(x)

}

if(is.character(x)){

if(!all( stri_enc_isutf8(x))){

x<-stri_encode(x, "", "UTF-8")

}

Encoding(x)<-"utf8"

}

#if R in Windows, convert columns and data to UTF-8

names(df)<-toutf8(names(df))

#下面的代码是偷懒，针对data.frame没有写，有兴趣的可以自己动手，使用data.table的好处还是快

library(data.table)

df<-as.data.table(df)

#if R in Windows, convert columns and data to UTF-8

if(Sys.info()[["sysname"]]=="Windows"){

names(df)<-toutf8(names(df))

df<-df[ , lapply(.SD, toutf8)]

dbGetQuery(con, "set NAMES utf8")

}

dbWriteTable(con, "data",df)

Currently browsing tag