数据处理 – 沙发说

查看数据库操作日志

把所有对数据库的操作/SQL(DDL,DML,Query)全都记录到文件或者表中，用于调试或查错。

Mysql

转自： Link

首先进入mysql输入指令

show variables like ‘gen%’;

可以看到输出

+——————+————————————-+

| Variable_name | Value |

+——————+————————————-+

| general_log | OFF |

| general_log_file | /usr/local/mysql/data/localhost.log |

+——————+————————————-+

可以看到general_log是开启还是环比状态，以及这个帐号的general_log文件在哪，设置开启

set global general_log=ON;

commit;//如果关闭了自动提交，记得commit一次结束事务

然后就可以去general_log_file的路径查看操作记录了

采用数据库内部查看

出了可以用日志文件的形式查看数据库操作记录之外，也可以把日志作为一个表单，在数据库内部查看

show variables like ‘%log_output%’;

可以看到输出，然后将其改为表单

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_output    | FILE  |

+---------------+-------+

set global log_output=’TABLE’;

之后就可以通过以下两句话查看数据库操作记录

select * from mysql.general_log; <=====查看操作记录

truncate table mysql.general_log; <=====清空操作记录表单

Postgresql

输出日志到文件，修改$PGDATA/postgresql.conf 可能$PGDATA=/var/lib/pgsql/9.4/data

log_destination = ‘stderr’

logging_collector = on

log_directory = ‘pg_log’

log_filename = ‘postgresql-%a.log’

#log_filename = ‘postgresql-%Y-%m-%d_%H%M%S.log’

log_statement = ‘mod’ # none, ddl, mod, all

日志文件只记录数据更新的操作,文件名如下

-rw——-. 1 postgres postgres 129 Jun 29 17:00 postgresql-Fri.log

如何查看

Linux下:

用 tail -f <logfilename> 来持续显示最新log

Windows下:

下载 tail.exe 放到 c:/windows目录下下载地址：/Files/hantianwei/tail.zip
或者使用GUI工具, BareTail: http://www.baremetalsoft.com/baretail/index.php 可以高亮关键字,比tail命令友好.

加载csv到postgresql

数据转移常用csv平文件，一般加载进数据库需要先创建表，然后加载csv到对应的表里。能不能在表不存在的情况下，加载csv到数据库，自动创建表呢？这对于快速分析很有用。

We can use tools like pgfutter to import flat delimiter files into postgres. It will create the table automatically ( all column type is text) or append data to existing table.

Refer to https://github.com/lukasmartinelli/pgfutter/

加载csv到postgrsql数据库，如果表存在，追加；如果表不存在，自动创建与文件名同名的表，字段默认都是text类型的。

./pgfutter_linux_amd64 –host “hostname” –port “5432” –db “dbname” –schema “schemaNam” –user “username” –pw “password” csv All-Rewards.csv

在数据库上，可以做数据类型的转换。可以直接编写查询sql，或者更改字段属性。

su – postgres

psql -h hostname -d dbname -U username -c “alter table schemaName.All_Rewards alter column REWARD_CONCURRENCE type integer using (trim(REWARD_CONCURRENCE)::integer);”

或者在SQL Client上改：

alter table sao_paulo_20171011 alter column id type integer using (trim(id)::integer);
alter table sao_paulo_20171011 alter column price type integer using (trim(price)::numeric(10));
alter table sao_paulo_20171011 alter column update_time type timestamp using (trim(case when update_time =” then null else update_time end)::timestamp);

postgresql 数据格式参考：

http://www.postgres.cn/docs/9.5/datatype.html
https://www.postgresql.org/docs/9.5/static/datatype.html

Linux版本下载：

https://github.com/lukasmartinelli/pgfutter/releases/download/v1.1/pgfutter_linux_amd64

R connect to DB via JDBC

使用JDBC连接数据库更通用，只要有对应数据库的JDBC driver，有Java环境，基本可以连上所有常见的数据库。所以我倾向于用客户端 Squirrel + jdbc 连接数据库进行一般的数据查询和处理。 R 中连接只需要以下几步。

R连接到Postgresql

查询用 dbGetQuery, DML语句用 dbSendQuery, DDL语句用 dbSendUpdate

library(RJDBC)
pDriver <- JDBC(driverClass=”org.postgresql.Driver”, classPath=”C:/Squirrel3.0/lib/postgresql-9.4.1211.jre6.jar”)
#pDriver <- JDBC(driverClass=”org.postgresql.util.PGJDBCMain”, classPath=”C:\\Squirrel3.0\\lib\\postgresql-9.4.1211.jre6.jar”)
pconn <- dbConnect(pDriver, “jdbc:postgresql://postgresql_svr_name_ip:5432/databasename?autosave=always”, “dbuser”, “password”)
input.data <- dbGetQuery(pconn, “select * from pg_tables limit 10”)
dbDisconnect(pconn) #可有可无

========================

R连接到Vertica

library(RJDBC)
vDriver <- JDBC(driverClass=”com.vertica.jdbc.Driver”, classPath=”C:/Squirrel3.0/lib/vertica-jdbc-7.0.1-0.jar”)
vconn <- dbConnect(vDriver, “jdbc:vertica://vertica_svr_name_ip:5433/databasename”, “dbuser”, “password”)
input.data <- dbGetQuery(vconn, “select * from tables limit 10”)

========================

R连接到Oracle

library(RJDBC)
oDriver <- JDBC(driverClass=”oracle.jdbc.OracleDriver”, classPath=”C:\\Squirrel3.0\\lib\\ojdbc6.jar”)
oconn <- dbConnect(oDriver, “jdbc:oracle:thin:@oracle_svr_name_ip:1521:databasename”, “dbuser”, “password”)
input.data <- dbGetQuery(oconn,”select * from all_tables limit 10″)

========================

R连接到Mysql

mDriver <- JDBC(driverClass=”com.mysql.jdbc.Driver”, classPath=”C:/Squirrel3.0/lib/mysql-connector-java-5.1.46.jar”)
mconn <- dbConnect(mDriver, “jdbc:mysql://mysql_svr_name_ip:3306/databasename”, “dbuser”, “password”)
input.data <- dbGetQuery(mconn, “select * from information_schema.tables limit 10”)

========================

R连接到Microsoft Sqlserver

兼容mssql 2003之前的版本

mssDriver <- JDBC(driverClass=”com.microsoft.sqlserver.jdbc.SQLServerDriver”, classPath=”C:/Squirrel3.0/lib/mssqljdbc41.jar”)
mssconn <- dbConnect(mssDriver , “jdbc:sqlserver://mssql_svr_name_ip:1433;databaseName=databasename”, “dbuser”, “password”)
input.data <- dbGetQuery(mssconn , “select * from sysobjects limit 10”)

========================

R连接到Teradata

tDriver<- JDBC(driverClass=”com.teradata.jdbc.TeraDriver”, classPath=”C:/Squirrel3.0/lib/terajdbc4.jar;C:/Squirrel3.0/lib/tdgssconfig.jar”)
tconn <- dbConnect(tDriver, “jdbc:teradata://Tera_svr_name_ip/TMODE=TERA”, “username”, “password”)
input.data <- dbGetQuery(tconn,”select top 10 * from dbc.dbcinfo”)

所用到的jar包都在Squerriel/lib目录下。下载 [小松鼠] Squirrel SQL Client Portable: https://1drv.ms/u/s!AtVaUU1SN60KhTXulW5JdrTNGF0E