一种替代方案可能是"镶木地板数据仓库".这里的前提是:
- 对于每个文件,读入CSV,根据需要格式化/增强/过滤/ Select ,然后另存为
.parquet
文件.
- 可选:在拆分一个或多个可索引(类别/序号)列后写入多个
.parquet
个文件.
- 使用
arrow::open_dataset
、dplyr
和他们的懒惰判断来读取数据.虽然这不允许您一次将整个数据集读取到内存中,但它确实允许您访问较小块中的所有数据,就像它是一个 Big Data 集一样.
对于内存中的工作,这仍然与data.table
兼容,使用arrow
懒惰地访问数据.虽然我下面的示例使用data.table
,但它不是必需的,并且实际上引入了一个或两个额外的步骤来将示例数据从tibble
转换为data.table
.我建议这样做是因为数据的性质很大,而且您标记了它,而不是因为它是必需的.
对于这两个示例,都需要注意一些事项:
- DataSet对象
ds
反映所有336,776行数据,尽管该对象相当小(仅仅是其中具有对文件和元数据的引用的环境).
- 上面的初始筛选返回一个惰性引用,而不是数据本身.要获得实际数据,需要加上
collect()
.不要试图对整个数据执行此操作,除非您知道它可以存储在内存中.
- 回想一下,原始对象是
data.table
(从fread
开始),write_parquet
保留了帧的几个属性(包括它),所以当我们意识到下面的数据时,它将是data.table
.
- 106,虽然
collect
ed数据为data.table
,但对该收集的数据所做的更改不会迁移回镶木地板文件本身.这意味着,如果你做了像collect(ds)[, newcol := 1]
这样的事情,那么再做collect(ds)
就不会有newcol
了.值得注意的是,parquet
个文件一旦写入就是不可变的:它们不能被更新或追加.
实际例子:nycflights13::flights
个.数据包含336,776行"Airline on-time data for all flights departing NYC in 2013".在本例中,我将数据随机分成5帧并保存为CSV文件.
set.seed(42)
ind <- sample(1:5, size = nrow(nycflights13::flights), replace = TRUE)
head(ind)
dir.create("csv")
i <- 1L
for (dat in split(nycflights13::flights, ind)) {
fwrite(dat, sprintf("csv/%s.csv", i))
i <- i + 1L
}
file.info(Sys.glob("csv/*"))
# size isdir mode mtime ctime atime exe
# csv/1.csv 6274623 FALSE 666 2022-09-19 05:21:40 2022-09-19 05:21:40 2022-09-19 05:21:54 no
# csv/2.csv 6265804 FALSE 666 2022-09-19 05:21:40 2022-09-19 05:21:40 2022-09-19 05:21:52 no
# csv/3.csv 6261533 FALSE 666 2022-09-19 05:21:40 2022-09-19 05:21:40 2022-09-19 05:21:43 no
# csv/4.csv 6260298 FALSE 666 2022-09-19 05:21:40 2022-09-19 05:21:40 2022-09-19 05:21:49 no
# csv/5.csv 6235815 FALSE 666 2022-09-19 05:21:40 2022-09-19 05:21:40 2022-09-19 05:21:46 no
对于下面的两个示例,我不打算进行任何数据过滤/格式化/增强;除了突出显示在何处执行此操作外,我假定您知道在保存之前需要对每个单独的CSV文件执行什么操作.
计划1:没有索引
dir.create("datamart")
for (fn in Sys.glob("csv/*.csv")) {
X <- fread(fn)
arrow::write_parquet(X, file.path("datamart", paste0(basename(fn), ".parquet")))
rm(X)
gc() # optional, might help
}
file.info(Sys.glob("datamart/*"))
# size isdir mode mtime ctime atime exe
# datamart/1.csv.parquet 1251629 FALSE 666 2022-09-19 05:26:28 2022-09-19 05:26:28 2022-09-19 05:35:59 no
# datamart/2.csv.parquet 1249485 FALSE 666 2022-09-19 05:26:45 2022-09-19 05:26:45 2022-09-19 05:35:59 no
# datamart/3.csv.parquet 1249652 FALSE 666 2022-09-19 05:26:47 2022-09-19 05:26:47 2022-09-19 05:35:59 no
# datamart/4.csv.parquet 1249772 FALSE 666 2022-09-19 05:26:48 2022-09-19 05:26:48 2022-09-19 05:35:59 no
# datamart/5.csv.parquet 1245022 FALSE 666 2022-09-19 05:26:49 2022-09-19 05:26:49 2022-09-19 05:35:59 no
读入数据:
library(dplyr)
library(arrow)
ds <- open_dataset("datamart")
nrow(ds)
# [1] 336776
object.size(ds) # environment
# 504 bytes
with(ls.objects(envir = ds), sum(Size))
# [1] 145888
ds %>%
filter(month == 1, between(day, 1, 10))
# FileSystemDataset (query)
# year: int32
# month: int32
# day: int32
# dep_time: int32
# sched_dep_time: int32
# dep_delay: int32
# arr_time: int32
# sched_arr_time: int32
# arr_delay: int32
# carrier: string
# flight: int32
# tailnum: string
# origin: string
# dest: string
# air_time: int32
# distance: int32
# hour: int32
# minute: int32
# time_hour: timestamp[us, tz=UTC]
# * Filter: ((month == 1) and ((day >= 1) and (day <= 10)))
# See $.data for the source Arrow object
ds %>%
filter(month == 1, between(day, 1, 10)) %>%
collect()
# year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time arr_delay carrier flight tailnum origin dest
# <int> <int> <int> <int> <int> <int> <int> <int> <int> <char> <int> <char> <char> <char>
# 1: 2013 1 1 554 600 -6 812 837 -25 DL 461 N668DN LGA ATL
# 2: 2013 1 1 555 600 -5 913 854 19 B6 507 N516JB EWR FLL
# 3: 2013 1 1 557 600 -3 709 723 -14 EV 5708 N829AS LGA IAD
# 4: 2013 1 1 558 600 -2 923 937 -14 UA 1124 N53441 EWR SFO
# 5: 2013 1 1 559 600 -1 941 910 31 AA 707 N3DUAA LGA DFW
# 6: 2013 1 1 607 607 0 858 915 -17 UA 1077 N53442 EWR MIA
# 7: 2013 1 1 613 610 3 925 921 4 B6 135 N635JB JFK RSW
# 8: 2013 1 1 615 615 0 833 842 -9 DL 575 N326NB EWR ATL
# 9: 2013 1 1 623 610 13 920 915 5 AA 1837 N3EMAA LGA MIA
# 10: 2013 1 1 624 630 -6 840 830 10 MQ 4599 N518MQ LGA MSP
# ---
# 8823: 2013 1 10 2038 2045 -7 2140 2154 -14 B6 1178 N640JB EWR BOS
# 8824: 2013 1 10 2040 2040 0 2351 2357 -6 B6 677 N809JB JFK LAX
# 8825: 2013 1 10 2054 2100 -6 2202 2207 -5 US 2144 N952UW LGA BOS
# 8826: 2013 1 10 2058 2100 -2 2229 2225 4 WN 530 N443WN LGA MDW
# 8827: 2013 1 10 2104 2110 -6 2337 2355 -18 B6 529 N507JB EWR MCO
# 8828: 2013 1 10 2129 2130 -1 148 218 -30 B6 701 N193JB JFK SJU
# 8829: 2013 1 10 2159 2159 0 2247 2300 -13 EV 4519 N13124 EWR BWI
# 8830: 2013 1 10 2320 2250 30 16 2354 22 B6 1018 N612JB JFK BOS
# 8831: 2013 1 10 NA 635 NA NA 940 NA AA 711 N3CDAA LGA DFW
# 8832: 2013 1 10 NA 700 NA NA 1007 NA UA 719 EWR DFW
# 5 variables not shown: [air_time <int>, distance <int>, hour <int>, minute <int>, time_hour <POSc>]
Plan B: using year
and month
as nested subdirectories
在您的数据中,可编制索引的字段可能是:
- 原始CSV文件名,如果您希望能够根据该文件名进行筛选
- 一个或多个固有的类别/序号字段(不连续)
在方便性和可用性之间有一个平衡:如果一个分类变量有20,000个可能的值,那么它可能太多,其中一个会失go 很大的效率.子目录中找到的目录/文件越多,呼叫open_dataset
所需的时间就越长,然后才能对其采取行动.我对此没有简单的衡量标准.
注:可以使用write_dataset
,这相当于上面的open_dataset
.它以同样的方式处理partitions=
个.但是,如果您不确定分区字段中的每个级别对于一个文件来说都是唯一的(例如,在我的样本数据中,所有CSV文件中有month == 1
个),那么每次读取CSV文件都会覆盖上一次写入的一些数据.在本例中,正如我将在这里演示的那样,我将手动写入子目录.
# ensures .parquet files are additive
addfile <- function(dat, base, by) {
thisdir <- do.call(file.path, as.list(c(base, paste(names(by), unname(by), sep = "="))))
dir.create(thisdir, recursive = TRUE, showWarnings = FALSE)
existing <- list.files(thisdir)
thisfile <- sprintf("%i.parquet", length(existing) + 1)
arrow::write_parquet(dat, file.path(thisdir, thisfile))
}
dir.create("datamart2")
for (fn in Sys.glob("csv/*.csv")) {
X <- fread(fn)
X[, addfile(.SD, "datamart2", by = .BY), by = .(year, month)]
rm(X)
gc() # optional
}
file.info(Sys.glob("datamart2/*/*/*"))
# size isdir mode mtime ctime atime exe
# datamart2/year=2013/month=1/1.parquet 133469 FALSE 666 2022-09-19 05:53:25 2022-09-19 05:53:25 2022-09-19 05:53:25 no
# datamart2/year=2013/month=1/2.parquet 132760 FALSE 666 2022-09-19 05:53:26 2022-09-19 05:53:26 2022-09-19 05:53:26 no
# datamart2/year=2013/month=1/3.parquet 134069 FALSE 666 2022-09-19 05:53:28 2022-09-19 05:53:28 2022-09-19 05:53:28 no
# datamart2/year=2013/month=1/4.parquet 132404 FALSE 666 2022-09-19 05:53:29 2022-09-19 05:53:29 2022-09-19 05:53:29 no
# datamart2/year=2013/month=1/5.parquet 136424 FALSE 666 2022-09-19 05:53:30 2022-09-19 05:53:30 2022-09-19 05:53:30 no
# datamart2/year=2013/month=10/1.parquet 140490 FALSE 666 2022-09-19 05:53:25 2022-09-19 05:53:25 2022-09-19 05:53:25 no
# datamart2/year=2013/month=10/2.parquet 139362 FALSE 666 2022-09-19 05:53:26 2022-09-19 05:53:26 2022-09-19 05:53:26 no
# datamart2/year=2013/month=10/3.parquet 138570 FALSE 666 2022-09-19 05:53:28 2022-09-19 05:53:28 2022-09-19 05:53:28 no
# datamart2/year=2013/month=10/4.parquet 137501 FALSE 666 2022-09-19 05:53:29 2022-09-19 05:53:29 2022-09-19 05:53:29 no
# datamart2/year=2013/month=10/5.parquet 137426 FALSE 666 2022-09-19 05:53:30 2022-09-19 05:53:30 2022-09-19 05:53:30 no
# datamart2/year=2013/month=11/1.parquet 133714 FALSE 666 2022-09-19 05:53:25 2022-09-19 05:53:25 2022-09-19 05:53:25 no
# datamart2/year=2013/month=11/2.parquet 134291 FALSE 666 2022-09-19 05:53:26 2022-09-19 05:53:26 2022-09-19 05:53:26 no
# datamart2/year=2013/month=11/3.parquet 133199 FALSE 666 2022-09-19 05:53:28 2022-09-19 05:53:28 2022-09-19 05:53:28 no
# datamart2/year=2013/month=11/4.parquet 136152 FALSE 666 2022-09-19 05:53:29 2022-09-19 05:53:29 2022-09-19 05:53:29 no
# datamart2/year=2013/month=11/5.parquet 133310 FALSE 666 2022-09-19 05:53:30 2022-09-19 05:53:30 2022-09-19 05:53:30 no
# datamart2/year=2013/month=12/1.parquet 141743 FALSE 666 2022-09-19 05:53:25 2022-09-19 05:53:25 2022-09-19 05:53:25 no
# datamart2/year=2013/month=12/2.parquet 142030 FALSE 666 2022-09-19 05:53:26 2022-09-19 05:53:26 2022-09-19 05:53:26 no
# datamart2/year=2013/month=12/3.parquet 139573 FALSE 666 2022-09-19 05:53:28 2022-09-19 05:53:28 2022-09-19 05:53:28 no
# datamart2/year=2013/month=12/4.parquet 140515 FALSE 666 2022-09-19 05:53:29 2022-09-19 05:53:29 2022-09-19 05:53:29 no
# datamart2/year=2013/month=12/5.parquet 140059 FALSE 666 2022-09-19 05:53:30 2022-09-19 05:53:30 2022-09-19 05:53:30 no
# datamart2/year=2013/month=2/1.parquet 126203 FALSE 666 2022-09-19 05:53:25 2022-09-19 05:53:25 2022-09-19 05:53:25 no
# datamart2/year=2013/month=2/2.parquet 126481 FALSE 666 2022-09-19 05:53:26 2022-09-19 05:53:26 2022-09-19 05:53:26 no
# datamart2/year=2013/month=2/3.parquet 126348 FALSE 666 2022-09-19 05:53:28 2022-09-19 05:53:28 2022-09-19 05:53:28 no
# datamart2/year=2013/month=2/4.parquet 126618 FALSE 666 2022-09-19 05:53:29 2022-09-19 05:53:29 2022-09-19 05:53:29 no
# datamart2/year=2013/month=2/5.parquet 123947 FALSE 666 2022-09-19 05:53:30 2022-09-19 05:53:30 2022-09-19 05:53:30 no
# datamart2/year=2013/month=3/1.parquet 140691 FALSE 666 2022-09-19 05:53:25 2022-09-19 05:53:25 2022-09-19 05:53:25 no
# datamart2/year=2013/month=3/2.parquet 142811 FALSE 666 2022-09-19 05:53:26 2022-09-19 05:53:26 2022-09-19 05:53:26 no
# datamart2/year=2013/month=3/3.parquet 142415 FALSE 666 2022-09-19 05:53:28 2022-09-19 05:53:28 2022-09-19 05:53:28 no
# datamart2/year=2013/month=3/4.parquet 140573 FALSE 666 2022-09-19 05:53:29 2022-09-19 05:53:29 2022-09-19 05:53:29 no
# datamart2/year=2013/month=3/5.parquet 138510 FALSE 666 2022-09-19 05:53:30 2022-09-19 05:53:30 2022-09-19 05:53:30 no
# datamart2/year=2013/month=4/1.parquet 140734 FALSE 666 2022-09-19 05:53:25 2022-09-19 05:53:25 2022-09-19 05:53:25 no
# datamart2/year=2013/month=4/2.parquet 140707 FALSE 666 2022-09-19 05:53:26 2022-09-19 05:53:26 2022-09-19 05:53:26 no
# datamart2/year=2013/month=4/3.parquet 140507 FALSE 666 2022-09-19 05:53:28 2022-09-19 05:53:28 2022-09-19 05:53:28 no
# datamart2/year=2013/month=4/4.parquet 141896 FALSE 666 2022-09-19 05:53:29 2022-09-19 05:53:29 2022-09-19 05:53:29 no
# datamart2/year=2013/month=4/5.parquet 141182 FALSE 666 2022-09-19 05:53:30 2022-09-19 05:53:30 2022-09-19 05:53:30 no
# datamart2/year=2013/month=5/1.parquet 139517 FALSE 666 2022-09-19 05:53:25 2022-09-19 05:53:25 2022-09-19 05:53:25 no
# datamart2/year=2013/month=5/2.parquet 140546 FALSE 666 2022-09-19 05:53:26 2022-09-19 05:53:26 2022-09-19 05:53:26 no
# datamart2/year=2013/month=5/3.parquet 143193 FALSE 666 2022-09-19 05:53:28 2022-09-19 05:53:28 2022-09-19 05:53:28 no
# datamart2/year=2013/month=5/4.parquet 139979 FALSE 666 2022-09-19 05:53:29 2022-09-19 05:53:29 2022-09-19 05:53:29 no
# datamart2/year=2013/month=5/5.parquet 141259 FALSE 666 2022-09-19 05:53:30 2022-09-19 05:53:30 2022-09-19 05:53:30 no
# datamart2/year=2013/month=6/1.parquet 143405 FALSE 666 2022-09-19 05:53:25 2022-09-19 05:53:25 2022-09-19 05:53:25 no
# datamart2/year=2013/month=6/2.parquet 142591 FALSE 666 2022-09-19 05:53:26 2022-09-19 05:53:26 2022-09-19 05:53:26 no
# datamart2/year=2013/month=6/3.parquet 142106 FALSE 666 2022-09-19 05:53:28 2022-09-19 05:53:28 2022-09-19 05:53:28 no
# datamart2/year=2013/month=6/4.parquet 143012 FALSE 666 2022-09-19 05:53:29 2022-09-19 05:53:29 2022-09-19 05:53:29 no
# datamart2/year=2013/month=6/5.parquet 141489 FALSE 666 2022-09-19 05:53:30 2022-09-19 05:53:30 2022-09-19 05:53:30 no
# datamart2/year=2013/month=7/1.parquet 145064 FALSE 666 2022-09-19 05:53:25 2022-09-19 05:53:25 2022-09-19 05:53:25 no
# datamart2/year=2013/month=7/2.parquet 143898 FALSE 666 2022-09-19 05:53:26 2022-09-19 05:53:26 2022-09-19 05:53:26 no
# datamart2/year=2013/month=7/3.parquet 144104 FALSE 666 2022-09-19 05:53:28 2022-09-19 05:53:28 2022-09-19 05:53:28 no
# datamart2/year=2013/month=7/4.parquet 146099 FALSE 666 2022-09-19 05:53:29 2022-09-19 05:53:29 2022-09-19 05:53:29 no
# datamart2/year=2013/month=7/5.parquet 146616 FALSE 666 2022-09-19 05:53:30 2022-09-19 05:53:30 2022-09-19 05:53:30 no
# datamart2/year=2013/month=8/1.parquet 145155 FALSE 666 2022-09-19 05:53:25 2022-09-19 05:53:25 2022-09-19 05:53:25 no
# datamart2/year=2013/month=8/2.parquet 143314 FALSE 666 2022-09-19 05:53:26 2022-09-19 05:53:26 2022-09-19 05:53:26 no
# datamart2/year=2013/month=8/3.parquet 145334 FALSE 666 2022-09-19 05:53:28 2022-09-19 05:53:28 2022-09-19 05:53:28 no
# datamart2/year=2013/month=8/4.parquet 144581 FALSE 666 2022-09-19 05:53:29 2022-09-19 05:53:29 2022-09-19 05:53:29 no
# datamart2/year=2013/month=8/5.parquet 145998 FALSE 666 2022-09-19 05:53:30 2022-09-19 05:53:30 2022-09-19 05:53:30 no
# datamart2/year=2013/month=9/1.parquet 135902 FALSE 666 2022-09-19 05:53:25 2022-09-19 05:53:25 2022-09-19 05:53:25 no
# datamart2/year=2013/month=9/2.parquet 135525 FALSE 666 2022-09-19 05:53:26 2022-09-19 05:53:26 2022-09-19 05:53:26 no
# datamart2/year=2013/month=9/3.parquet 136012 FALSE 666 2022-09-19 05:53:28 2022-09-19 05:53:28 2022-09-19 05:53:28 no
# datamart2/year=2013/month=9/4.parquet 137506 FALSE 666 2022-09-19 05:53:29 2022-09-19 05:53:29 2022-09-19 05:53:29 no
# datamart2/year=2013/month=9/5.parquet 133894 FALSE 666 2022-09-19 05:53:30 2022-09-19 05:53:30 2022-09-19 05:53:30 no
读取数据的过程与第1部分相同,不过请注意,索引year
和month
是最后两列,而不是前两列:
library(dplyr)
library(arrow)
ds <- open_dataset("datamart2")
nrow(ds)
# [1] 336776
object.size(ds) # environment
# 504 bytes
with(ls.objects(envir = ds), sum(Size))
# [1] 155896
ds %>%
filter(month == 1, between(day, 1, 10))
# FileSystemDataset (query)
# day: int32
# dep_time: int32
# sched_dep_time: int32
# dep_delay: int32
# arr_time: int32
# sched_arr_time: int32
# arr_delay: int32
# carrier: string
# flight: int32
# tailnum: string
# origin: string
# dest: string
# air_time: int32
# distance: int32
# hour: int32
# minute: int32
# time_hour: timestamp[us, tz=UTC]
# year: int32
# month: int32
# * Filter: ((month == 1) and ((day >= 1) and (day <= 10)))
# See $.data for the source Arrow object
ds %>%
filter(month == 1, between(day, 1, 10)) %>%
collect()
# day dep_time sched_dep_time dep_delay arr_time sched_arr_time arr_delay carrier flight tailnum origin dest air_time
# <int> <int> <int> <int> <int> <int> <int> <char> <int> <char> <char> <char> <int>
# 1: 1 517 515 2 830 819 11 UA 1545 N14228 EWR IAH 227
# 2: 1 542 540 2 923 850 33 AA 1141 N619AA JFK MIA 160
# 3: 1 544 545 -1 1004 1022 -18 B6 725 N804JB JFK BQN 183
# 4: 1 557 600 -3 838 846 -8 B6 79 N593JB JFK MCO 140
# 5: 1 558 600 -2 849 851 -2 B6 49 N793JB JFK PBI 149
# 6: 1 559 600 -1 854 902 -8 UA 1187 N76515 EWR LAS 337
# 7: 1 600 600 0 851 858 -7 B6 371 N595JB LGA FLL 152
# 8: 1 615 615 0 1039 1100 -21 B6 709 N794JB JFK SJU 182
# 9: 1 635 635 0 1028 940 48 AA 711 N3GKAA LGA DFW 248
# 10: 1 655 655 0 1021 1030 -9 DL 1415 N3763D JFK SLC 294
# ---
# 8823: 10 2038 2045 -7 2140 2154 -14 B6 1178 N640JB EWR BOS 40
# 8824: 10 2040 2040 0 2351 2357 -6 B6 677 N809JB JFK LAX 343
# 8825: 10 2054 2100 -6 2202 2207 -5 US 2144 N952UW LGA BOS 34
# 8826: 10 2058 2100 -2 2229 2225 4 WN 530 N443WN LGA MDW 117
# 8827: 10 2104 2110 -6 2337 2355 -18 B6 529 N507JB EWR MCO 127
# 8828: 10 2129 2130 -1 148 218 -30 B6 701 N193JB JFK SJU 186
# 8829: 10 2159 2159 0 2247 2300 -13 EV 4519 N13124 EWR BWI 33
# 8830: 10 2320 2250 30 16 2354 22 B6 1018 N612JB JFK BOS 35
# 8831: 10 NA 635 NA NA 940 NA AA 711 N3CDAA LGA DFW NA
# 8832: 10 NA 700 NA NA 1007 NA UA 719 EWR DFW NA
# 6 variables not shown: [distance <int>, hour <int>, minute <int>, time_hour <POSc>, year <int>, month <int>]
备注:
- 如果您的系统能够做到这一点,那么可能值得您努力迭代每个
year=?/month=?
个子目录,并将所有数据合并到一个文件中.还记得我提到的"文件多,慢open_dataset
"吗?组合同级.parquet
文件可能有助于解决此问题.同样,只有当您一次可以将一个子目录集的所有内容读入内存时,这才是可能的.这可能没有必要,交给你吧.
- 我使用了"hive"分区/子目录方案,其中
year=
隐式创建可索引的列.(如果您阅读1.parquet
或directory中的一个,您会注意到其中没有year
或month
.)如果这不是您想要的,您可以创建更简单的路径名(例如,2013/12/1.parquet
)并使用open_dataset("datamart2", partitions=c("year","month"))
.