搜索此博客

2015年12月31日星期四

ORE 使用说明

大多数Oracle R Enterprise的方法和对象以ore为前缀
,ore表明它存储了对应于Oracle 数据库的元数据。ore.frame为Oracle R Enterprise元数据对象,对应于数据库中的表。
ore.frame类似于R中的数据框。
ore.drop(table="IRIS_TABLE")
iris$id <- 1:150
ore.create(iris,table="IRIS_TABLE")

#加载R data frame到Database
ore.create(data_frame, table="TABLE_NAME")
#将R对象存储到数据库中作为一个临时对象,并返回用于处理的ore对象。
ore.push(data.frame)
#测试是否在ORE schema中存在ore.frame对象。但必须先用 ore.sync()进行同步。
ore.exists()
#删除数据库中的表v。
ore.drop(table="v")
#将数据库中的NARROW表或视图创建为R中名为df_narrow数据框,需要注意数据框的大小,防止超过R的限制。
df_narrow <- ore.pull(NARROW)

#数据库中的表没有定义行的数据,不能完全映射到R中的数据结构。可以在数据库中使用ORDER BY语句。
#row.names提供定义排序,但没有添加索引。可用在单列或多列上, row.names用在未排序的数据,产生错误。
#ORE object has no unique key - using random order
#检索指定的ore.frame对象,代表数据库中的表或视图,在Oracle R Enterprise会话中提供schema。
IRIS_TABLE <- ore.get("IRIS_TABLE")
#将一个无重复无缺失值的变量作为数据框的行名属性
row.names(IRIS_TABLE) <- IRIS_TABLE$id
#查看结果
row.names(head(IRIS_TABLE))
#按照索引列出
IRIS_TABLE[c(1L,2L,3L),]


#将R对象存贮在数据库中,使用当点用户的schema
ore.save(iris,name="iris",append = F)
#加载指定数据库中的R对象
ore.load("iris")
#从用户的schema中删除指定的数据存储
ore.delete("iris")
#列出用户schema中的数据存储和基本描述信息
ore.datastore()
#返回一个描述数据存储的名字和汇总信息的dataframe
ore.datastoreSummary("iris")

ore.sync()
ore.ls()

set.seed(123)
N <- 1000000
mydata <- data.frame(x = rnorm(N, mean = 20, sd = 2),
                     group = sample(letters, N, replace = TRUE,
                              prob = (26:1)/sum(26:1)))
mydata$y <-rbinom(N, 1,
         1/(1+exp(-(.5 - 0.25 * mydata$x + .1 * as.integer(mydata$group)))))
MYDATA <- ore.push(mydata)

rm(mydata)
# Create a function that creates random row indices from large tables
mysampler <- function(n, size, replace = FALSE)
{
  #' Random Whole Number Sampler
  #' @param n number of observations in sample
     #' @param size total number of observations
     #' @param replace indicator for sampling with replacement
     #' @return numeric vector containing the sample indices
     n <- round(n)
     size <- round(size)
     if (n < 0) stop("'n' must be a non-negative number")
     if (size < 1) stop("'size' must be a positive number")
     if (!replace && (n > size))
       stop("'n' cannot exceed 'size' when 'replace = FALSE'")
     if (n == 0)
       numeric()
     else if (replace)
       round(runif(n, min = 0.5, max = size + 0.5))
     else
     {
       maxsamp <- seq(size + 0.5, by = -1, length.out = n)
       samp <- round(runif(n, min = 0.5, max = maxsamp))
       while(length(bump1 <- which(duplicated(samp))))
         samp[bump1] <- samp[bump1] + 1
       samp
     }
}

N <- nrow(MYDATA)
sampleSize <- 500
#简单随机抽样Simple random sampling
srs <- mysampler(sampleSize, N)
simpleRandomSample <- ore.pull(MYDATA[srs, , drop = FALSE])

#系统抽样Systematic sampling
systematic <- round(seq(1, N, length.out = sampleSize))
systematicSample <- ore.pull(MYDATA[systematic, , drop = FALSE])

#分层抽样Stratified sampling
stratifiedSample <-
  do.call(rbind,lapply(split(MYDATA, MYDATA$group),
                 function(y){
                   ny <- nrow(y)
                   y[mysampler(sampleSize * ny/N, ny), , drop = FALSE]
                 }))
#整群抽样Cluster sampling
clusterSample <- do.call(rbind, sample(split(MYDATA, MYDATA$group), 2))

#便利抽样Accidental/Convenience sampling(via row order access)
convenientSample1 <- head(MYDATA, sampleSize)

#便利抽样Accidental/Convenience sampling (via hashing)
maxHash <- 2^32 # maximum allowed in ore.hash
convenient2 <- (ore.hash(rownames(MYDATA), maxHash)/maxHash) <= (sampleSize/N)
convenientSample2 <- ore.pull(MYDATA[convenient2, , drop = FALSE])

#随机分组(Random Partitioning)
#靠给透明层中的ore.frame增加一个分组变量完成
k <- 5
nrowX <- nrow(IRIS_TABLE)
IRIS_TABLE$partition <- sample(rep(1:k, each = nrowX/k, length.out = nrowX), replace =
                        TRUE)

results <- ore.groupApply(IRIS_TABLE, IRIS_TABLE$partition, function(y) {...}, parallel = TRUE)

Error: role "RQADMIN" is required to perform this operation


运行ore.groupApply(IRIS_TABLE, IRIS_TABLE$partition, function(y) {...}, parallel = TRUE)
提示
Error: role "RQADMIN" is required to perform this operation
解决
sqlplus c##RQUSER/rquser@pdborcl
grant RQADMIN to c##rquser;

2015年12月28日星期一

oracle pdb数据库操作

#查看所有的用户
select * from all_users;

#给用户登录权限
grant connect to RQUSER;
grant connect to RQSYS;

#用户解锁
alter user RQSYS account unlock;

#查看当前数据库是否为多租户数据库,v$database的cdb字段显示yes,表明当前数据库为一个CDB数据库:
select name,cdb from v$database;

#查看当前的CDB数据库中pdb的状态:
Select name,con_id,open_mode from v$pdbs;
有mounted和redad wirte两种状态。PDB$SEED,类似于master数据库。
select pdb_id,pdb_name,dbid,status,creation_scn from dba_pdbs;

#修改pdb数据库的状态为READ WRITE
Alter pluggable database PDBORCL open;

#查看当前连接的是cdb还是pdb
select name,open_mode from v$database;

#使用命令show con_name可以查看当前连接的是哪个数据库
show con_name;

#切换cdb和pdb数据库
alter session set container=pdborcl;
show con_name;
alter session set container = cdb$root;

#创建公用用户RQUSER, 需要使用C##或者c##作为该用户名的开头
#在Oracle 12C中,账号分为两种,一种是公用账号,一种是本地账号(亦可理解为私有账号)。共有账号是指在CDB下创建,并在全部PDB中生效的账号,另一种是在PDB中创建的账号。
create user c##RQUSER identified by rquser;
grant dba to c##RQUSER container = all;

公用用户分别登录CDB、PDB看看是否通用
sqlplus c##RQUSER/rquser
sqlplus c##RQUSER/rquser@pdborcl

conn c##RQUSER/rquser;

#添加表和内容
create table test(id number(2),name varchar(20));
insert into test values(1,'xuefliang');
#提交
commit;

#显示当前用户
show user;

#关闭pdb数据库
alter pluggable database pdborcl close;

#打开和关闭所有pdb数据库
alter pluggable database all open;
alter pluggable database all close;

2015年12月23日星期三

Error in if (timezone != "")

ORE连接出现
Error in if (timezone != "") try(.ore.dbGetQuery(sprintf("alter session set time_zone = '%s'",  : 
  missing value where TRUE/FALSE needed
解决办法:
Sys.setenv(TZ = "EDT") 

ORA-21561: OID generation failed

sudo vi /etc/hosts
添加
10.112.6.251  xuefliang

Setting up ORE 1.4.1 with Oracle 12c (12.1.0.1) On Windows Machine.

1、在sqlplus中执行(sqlplus / as sysdba)
Select name,con_id,open_mode from v$pdbs;
#修改 pluggable database status to READ WRITE.
Alter pluggable database PDBORCL open;
Select name,con_id,open_mode from v$pdbs;

2、下载ore-server-win-x86_64-1.4.1.zip和ore-supporting-win-x86_64-1.4.1.zip,解压后,将server、supporting和server.bat复制c:\Windows\system32下,以管理员身份运行server.bat --pdb PDBORCL --perm SYSAUX  --temp TEMP  --rqsys rqsys --user-perm SYSAUX  --user-temp TEMP  --pass rquser  --user RQUSER

3、R
library(ORE)
Sys.setenv(TZ = "EDT")
ore.connect(user="system", service_name="PDBORCL", host="10.112.6.251", password="52332580", port=1521, all=F)
ore.is.connected()
ore.sync()
ore.ls()
ore.exists("MVIEW_FILTER")
ore.get("MVIEW_FILTER")
ore.disconnect()

参考:https://explorebigdataanalytics.wordpress.com/2014/12/16/setting-up-ore-1-4-1-with-oracle-12c-12-1-0-1-r-3-1-2-on-windows-machine/

https://docs.oracle.com/cd/E57012_01/doc.141/e57007/toc.htm

2015年12月21日星期一

Install RODBC with Oracle 12c ODBC in Ubuntu 14.04 64-bit

1、安装 Oracle Instant Cilent

sudo alien -i oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm
sudo alien -i oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64.rpm
sudo alien -i oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm
sudo alien -i oracle-instantclient12.1-odbc-12.1.0.2.0-1.x86_64.rpm

2、sudo gedit /etc/ld.so.conf.d/oracle.conf
添加  /usr/lib/oracle/12.1/client64/lib
sudo ldconfig

# 导致不能正常启动
# sudo gedit  /etc/profile.d/oracle.sh
# 添加 export ORACLE_HOME= /usr/lib/oracle/12.1/client64

3、安装 unixodbc
sudo apt-get install unixodc
检查64-bit unixodbc

which isql
file <the output above>

结果类似
ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.24, BuildID

4、配置连接
sudo gedit /etc/odbc.ini
添加
[MYORACLEDSN]
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
CloseCursor = F
DisableDPM = F
DisableMTS = T
Driver = Oracle
EXECSchemaOpt =
EXECSyntax = T
Failover = T
FailoverDelay = 10
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
Lobs = T
Longs = T
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
ServerName = //10.112.0.6:1521/orcl
SQLGetData extensions = F
Translation DLL =
Translation Option = 0
UserName = "gssvac"
Password = "gssvac"

sudo gedit  /etc/odbcinst.ini
添加
[Oracle]
Description = Oracle ODBC Connection
Driver = /usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1
Setup =
FileUsage =
CPTimeout =
CPReuse =

5、 测试
isql MYORACLEDSN gssvac gssvac

可能出现问题
ldd /usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1

出现
libodbcinst.so.2 => not found
sudo updatedb
locate libodbcinst
结果
/usr/lib/x86_64-linux-gnu/libodbcinst.so.1
/usr/lib/x86_64-linux-gnu/libodbcinst.so.1.0.0

解决
sudo ln -s /usr/lib/x86_64-linux-gnu/libodbcinst.so.1 /usr/lib/libodbcinst.so.2


library(RODBC)
channel <- odbcConnect("MYORACLEDSN",uid="gssvac",pwd="gssvac")
tb_zyf <- sqlQuery(channel,'SELECT * FROM sys_log')
odbcClose(channel)

2015年12月12日星期六

安装ROracle

一、Linux mint
1、下载instantclient-basic-linux.x64-12.1.0.2.0.zip、instantclient-sdk-linux.x64-12.1.0.2.0.zip和instantclient-sqlplus-linux.x64-12.1.0.2.0.zip

2、
unzip instantclient-basic-linux.x64-12.1.0.2.0.zip
unzip instantclient-sqlplus-linux.x64-12.1.0.2.0.zip
unzip instantclient-sdk-linux.x64-12.1.0.2.0.zip

ln -s libclntsh.so.12.1 libclntsh.so
ln -s libocci.so.12.1 libocci.so

export LD_LIBRARY_PATH=$(pwd)

./sqlplus  #测试


3、R CMD INSTALL --configure-args='--with-oci-inc=/home/xuefliang/instantclient_12_1/sdk/include --with-oci-lib=/home/xuefliang/instantclient_12_1/' /home/xuefliang/ROracle_1.2-1.tar.gz

mkdir rdbms
cd rdbms
ln -s /home/xuefliang/Downloads/instantclient_12_1/sdk/include  public
cd ..
mkdir lib
#复制 instantclient_12_1 下所有文件到 lib目录
cp ./* ./lib

sudo vi /etc/ld.so.conf.d/oracle.conf && sudo chmod o+r /etc/ld.so.conf.d/oracle.conf

add the oracle library path

/usr/lib/oracle/12.1/client64/lib/

add the oracle library path

/home/xuefliang/Downloads/instantclient_12_1/lib

sudo ldconfig

Sys.setenv(ORACLE_HOME='/home/xuefliang/Downloads/instantclient_12_1')

install.packages("ROracle")

方式二:
sudo apt-get install alien

alien -i oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm
alien -i oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64.rpm
alien -i oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm

export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib/

sudo vi /etc/ld.so.conf.d/oracle.conf && sudo chmod o+r /etc/ld.so.conf.d/oracle.conf

add the oracle library path

/usr/lib/oracle/12.1/client64/lib/

sudo ldconfig

export ORACLE_HOME=/usr/lib/oracle/12.1/client64

sudo ln -s /usr/include/oracle/12.1/client64 $ORACLE_HOME/include

mkdir /usr/lib/oracle/12.1/client64/rdbms

sudo ln -s /usr/include/oracle/12.1/client64 $ORACLE_HOME/rdbms/public

sqlplus  sqlplus64 #测试

R CMD INSTALL --configure-args='--with-oci-inc=/usr/lib/oracle/12.1/client64/include --with-oci-lib=/usr/lib/oracle/12.1/client64/lib' /home/xuefliang/ROracle_1.2-1.tar.gz

Sys.setenv(ORACLE_HOME='/usr/lib/oracle/12.1/client64')
library(ROracle)

二、windows 10(64位)
1、下载并安装Rtools
2、环境变量-系统变量-Path中添加C:\Rtools\bin;C:\Rtools\gcc-4.6.3\bin;
3、下载并解压instantclient-basic-windows.x64-12.1.0.2.0.zip、instantclient-sdk-windows.x64-12.1.0.2.0.zip和instantclient-sqlplus-windows.x64-12.1.0.2.0.zip,解压到一个文件夹中instantclient_12_1中。
4、环境变量-系统变量添加OCI_INC C:\instantclient\x64\instantclient_12_1\sdk\include
OCI_LIB64 C:\instantclient\x64\instantclient_12_1\
5、安装DBI包
6、重启后(环境变量生效)
  cmd cd C:\Program Files\R\R-3.2.2\bin\x64

2015年12月11日星期五

R 连接 Oracle

一、windows
1、安装java
2、下载并解压 instantclient-basic-win32-11.2.0.1.0
3、
方式一 RODBC
library(RODBC)
#本地连接
channel <- odbcConnect("DSN_xuefliang",uid="system",pwd="52332580")
tb_zyf <- sqlQuery(channel,'SELECT * FROM t')
odbcClose(channel)

#不需要设置ODBC
dbconnection <- odbcDriverConnect("Driver=Oracle in OraDB12Home1;
                                  Server=127.0.0.1; Database=orcl;
                                  uid=system; pwd=52332580")

#远程连接
#下载instantclient-odbc-windows.x64-12.1.0.2.0.zip
#1、odbc配置TNS service name 10.112.0.6:1521/orcl
channel <- odbcConnect("dsn",uid="gssvac",pwd="gssvac")
tb_zyf <- sqlQuery(channel,'SELECT * FROM sys_log')
odbcClose(channel)


tb_zyf <- sqlQuery(dbconnection,'SELECT * FROM t')
odbcClose(dbconnection)

方式二 RJDBC
Sys.setenv(JAVA_HOME='C:\\Program Files\\Java\\jre1.8.0_66')
library(RJDBC)
#本地连接
drv <- JDBC("oracle.jdbc.OracleDriver",
            classPath="C:\\app\\Admin\\product\\instantclient_11_2\\ojdbc5.jar", " ")
con <- dbConnect(drv, "jdbc:oracle:thin:@127.0.0.1:1521:orcl", "system", "52332580")
#dbWriteTable(con, "TEST_TABLE", t)
dbGetQuery(con, "select * from T")
dbDisconnect(con)

#远程连接
drv <- JDBC("oracle.jdbc.OracleDriver",
            classPath="C:\\app\\Admin\\product\\instantclient_11_2\\ojdbc5.jar", " ")
con <- dbConnect(drv, "jdbc:oracle:thin:@10.112.0.6:1521:orcl", "gssvac", "gssvac")
log <- dbGetQuery(con, "select * from sys_log")
names(log)
head(log$USER_NAME)
dbDisconnect(con)

方式三 ROracle
library(ROracle)
drv <- dbDriver("Oracle")
connect.string <- paste(
  "(DESCRIPTION=",
  "(ADDRESS=(PROTOCOL=tcp)(HOST=10.112.0.6)(PORT=1521))",
  "(CONNECT_DATA=(SERVICE_NAME=orcl)))", sep = "")
#疫苗
con <- dbConnect(drv, username = "gssvac", password = "gssvac",
                 dbname = connect.string)
depart <- dbGetQuery(con, "select * from sys_log")
dbDisconnect(con)

#金卫信
con <- dbConnect(drv, username = "vaccuser", password = "gscdc20140516",
                 dbname = connect.string)

depart <- dbGetQuery(con, "select * from department")
child <- dbGetQuery(con, "select count(CHIL_NAME) from child")
inoculation <- dbGetQuery(con,"select * from inoculation")
bacterin <- dbGetQuery(con,"select * from bacterin")
dbDisconnect(con)

二、linux
方式一 RJDBC
1、下载instantclient_12_1,并解压。
2、
library(RJDBC)
drv <- JDBC("oracle.jdbc.OracleDriver",
            classPath="/home/xuefliang/Downloads/instantclient_12_1/ojdbc6.jar", " ")
#疫苗
#服务器:管理工具-高级Window防火墙-入站规则-新建规则-端口:1521
con <- dbConnect(drv, "jdbc:oracle:thin:@10.112.0.6:1521:orcl", "gssvac", "gssvac")
log <- dbGetQuery(con, "select * from sys_log")
dbDisconnect(con)

#金卫信
con <- dbConnect(drv, "jdbc:oracle:thin:@10.112.0.6:1521:orcl", "vaccuser", "gscdc20140516")
depart <- dbGetQuery(con, "select * from department")
dbDisconnect(con)

方式二 ROracle
Sys.setenv(ORACLE_HOME='/usr/lib/oracle/12.1/client64')
#疫苗
#select userenv("language") from dual; 查询编码集
Sys.setenv(NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK")
library(ROracle)
drv <- dbDriver("Oracle")
connect.string <- paste(
  "(DESCRIPTION=",
  "(ADDRESS=(PROTOCOL=tcp)(HOST=10.112.0.6)(PORT=1521))",
  "(CONNECT_DATA=(SERVICE_NAME=orcl)))", sep = "")
con <- dbConnect(drv, username = "gssvac", password = "gssvac",
                 dbname = connect.string)

log <- dbGetQuery(con, "select * from sys_log")
log$USER_NAME <- iconv(log$USER_NAME,from="GBK",to="UTF-8")
log$REGION_NAME <- iconv(log$REGION_NAME,from="GBK",to="UTF-8")
dbDisconnect(con)

#金卫信
Sys.setenv(NLS_LANG="AMERICAN_AMERICAN.ZHS16GBK")
library(ROracle)
drv <- dbDriver("Oracle")
connect.string <- paste(
  "(DESCRIPTION=",
  "(ADDRESS=(PROTOCOL=tcp)(HOST=10.112.0.6)(PORT=1521))",
  "(CONNECT_DATA=(SERVICE_NAME=orcl)))", sep = "")
con <- dbConnect(drv, username = "vaccuser", password = "gscdc20140516",
                 dbname = connect.string)

depart <- dbGetQuery(con, "select * from department")
depart$DEPA_NAME <- iconv(depart$DEPA_NAME,from="GBK",to="UTF-8")
dbDisconnect(con)

2015年12月9日星期三

ubuntu install ibus-libpinyin

1、sudo apt-get install libglib2.0-dev libdb-dev libgtk-3-dev gnome-common libibus-1.0-dev sqlite3
2、下载libpinyin-1.3.0.tar.gz( git clone https://github.com/libpinyin/libpinyin)
./autogen.sh
make
sudo make install
3、下载ibus-libpinyin-1.7.3( git clone https://github.com/libpinyin/ibus-libpinyin)
./autogen.sh
make
sudo make install

4、 sudo cp /usr/local/share/ibus/component/libpinyin.xml /usr/share/ibus/component/

2015年12月1日星期二

Rstudio 使用代理

启动Rstudio 后运行
Sys.setenv(http_proxy="http://127.0.0.1:1080")
Sys.getenv("http_proxy")
成功

getwd()获得工作目录,在此工作目录下有.Renviron文件,修改文件内容为:
http_proxy="http://127.0.0.1:1080"
在Rstudio启动后,可以用Sys.getenv("http_proxy")进行测试。

1、在VPS上安装OpenConnect server(参考http://www.fanyueciyuan.info/fq/ocserv-debian.html)
2、PC上安装Cisco AnyConnect Secure Mobility VPN(下载地址:http://helpdesk.ugent.be/vpn/en/asa.php)

sudo apt-get install network-manager-openconnect
sudo bash vpn_install.sh

3、使用144.168.56.190:999地址,用户名和密码进行连接。实现全局代理。 
测试通过。



ubuntu 安装 privoxy

1、安装
sudo proxychains apt-get install privoxy

2、配置 privoxy
sudo gedit /etc/privoxy/config。
编辑它增加一行:
forward-socks5 / 127.0.0.1:1080 .

forward-socks5代表转发到socks5代理,/代表所有的URL都转发(也可以在这里写url patten),127.0.0.1:1080是socks代理的位置,最后的一点.代表没有http代理
privoxy 的监听端口号,默认是 8118,默认绑定的地址是 127.0.0.1(这个地址代表“当前系统”)。由于默认是绑定在 127.0.0.1 这个地址,所以只有当前系统的软件才可以连接到 Privoxy 的监听端口。
修改 privoxy 的监听端口和地址
在尾部增加如下一行
listen-address 0.0.0.0:8118

重启  sudo /etc/init.d/privoxy restart

3、使用:127.0.0.1:8118