UNIX将LARGE csv导入SQLite [英] UNIX Importing LARGE csv into SQLite

问题描述

我有一个5gig csv文件（也作为sas数据文件，如果它会更容易），我需要放入一个sql数据库，所以我可以在R中使用它。

I have a 5gig csv file (also as a sas datafile, if it would be easier) which I need to put into a sql database so I can work with it in R.

变量名称都包含在第一个观察行中，并用双引号括起来。有1000多个变量一些数字的其他字符（虽然一些字符变量是数字串，但我不太担心它，我可以解决它在R）。

The variables names are all contained in the first observation line and are double quoted. There are 1000+ variables some of numeric others character (though some of the character variables are strings of numerals, but I'm not too worried about it I can fix it in R).

我的问题是如何以最小的痛苦将csv文件导入我的数据库中的新表？

My question is how can I import the csv file into a new table in my database with minimal pain?

我发现有东西说要创建你的表首先（包括指定所有的变量，我有1000+），然后使用.import文件表来引入数据。
或者，使用一些gui导入向导，这对我来说不是一个选项。

I've found things saying to create your table first (which includes specifying all the variables, of which I have 1000+) and then using ".import file table" to bring in the data. Or, to use some gui import wizard, which is not an option for me.

对不起，如果这是sql 101但感谢您的帮助。 p>

Sorry if this is sql 101 but thanks for the help.

推荐答案

这是我的工作流程：

library("RSQLite")
setwd("~/your/dir")
db <- dbConnect(SQLite(), dbname="your_db.sqlite") ## will make, if not present
field.types <- list(
        date="INTEGER",
        symbol="TEXT",
        permno="INTEGER",
        shrcd="INTEGER",
        prc="REAL",
        ret="REAL")
dbWriteTable(conn=db, name="your_table", value="your_file.csv", row.names=FALSE, header=TRUE, field.types=field.types)
dbGetQuery(db, "CREATE INDEX IF NOT EXISTS idx_your_table_date_sym ON crsp (date, symbol)")
dbDisconnect(db)

不需要 field.types 。如果你不提供这个列表，RSQLite将从头中猜出。该索引不是必需的，但会稍后加快您的查询（如果您为您的查询索引正确的列）。

The field.types isn't necessary. RSQLite will guess from the header if you don't provide this list. The index isn't required either, but will speed up your queries later on (if you index the correct column for your queries).

我一直在学习这个东西在这里的SO，所以如果你检查我的问题提出/回答SQLite，你可能会找到一些标签的东西。

I've been learning a lot of this stuff here on SO, so if you check my questions asked/answered on SQLite, you may find some tagential stuff.

这篇关于UNIX将LARGE csv导入SQLite的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！