Подтвердить что ты не робот

Оптимальный дизайн базы данных с точки зрения скорости запроса для хранения матриц из R

У меня есть сотни матриц, которые нужно использовать в R, и большинство из них составляют около 45000x350 каждый. Я бы хотел найти оптимальный выбор программного обеспечения базы данных и схему для хранения данных и возможность вызова подмножеств матриц из базы данных. Это необходимо сделать, чтобы объект извлечения данных был как можно быстрее.

В качестве базы здесь используется код, который создает 5 матриц, похожих на то, с чем я имею дело:

if(!"zoo" %in% installed.packages()[,1]) { install.packages("zoo") }
require("zoo", quietly=TRUE)

numSymbols <- 45000
numVariables <- 5
rDatePattern <- "%d/%m/%Y"
startDate <- "31/12/1982"
endDate <- "30/09/2011"
startYearMonth <- as.yearmon(startDate,format=rDatePattern)
alphaNumeric <- c(1:9,toupper(letters))             
numMonths <- (as.yearmon(endDate,format=rDatePattern)-startYearMonth)*12
numValues <- numSymbols*(numMonths+1)

dateVector <- sapply(1:(numMonths+1), function(x) {as.character(format(as.Date(startYearMonth+x*1/12,fraq=0)-1,rDatePattern))})

symbolNames <- sapply(1:numSymbols,function(x) {as.character(paste((sample(alphaNumeric,7)),collapse=""))})

for(i in 1:numVariables) {
    assign(paste("Variable",i,sep="_"),matrix(sample(c(rnorm(numValues/2),rep(NA,numValues/2))),
                                              nrow=numSymbols,
                                              ncol=(numMonths+1),
                                              dimnames=list(symbolNames,dateVector)))
}

В основном все матрицы будут иметь примерно половину значений, заполненных удвоениями, и остальных NA.

# > ls()[grepl("Variable_",ls())]
# [1] "Variable_1" "Variable_2" "Variable_3" "Variable_4" "Variable_5"

# > dim(Variable_1)
# [1] 45000   346

# > Variable_1[1:10,1:3]
#                  31/12/1982          31/01/1983           28/02/1983
# AF3HM5V                  NA                  NA -1.15076100366945755
# DL8TVIY                  NA                  NA -1.59412257037490046
# JEFDYPO                  NA                  NA                   NA
# 21ZV689                  NA                  NA -0.31095014405320764
# RP1DZHB -1.0571670785223215 -0.7206356272944392 -0.84028668343265112
# N6DUSZC                  NA                  NA -1.31113363079930023
# XG3ZA1W                  NA  0.8531074740045220  0.06797987526470438
# W1JCXIE  0.2782029710832690 -1.2668560986048898                   NA
# X3RKT2B  1.5220172324681460 -1.0460218516729356                   NA
# 3EUB8VN -0.9405417187846803  1.1151437940206490  1.60520458945005262

Я хочу иметь возможность хранить их в базе данных. RDBMS будет вариантом по умолчанию, но я готов посмотреть другие варианты. Самая большая часть - это оптимальное решение для быстрого запроса, будь то для всей матрицы или подмножества матрицы, например. 2000 символов, 100 дат и т.д.

Текущее решение, которое я использовал, сохраняет каждую матрицу как файл RData, а затем загружается по всей матрице и усекает ее размер. Это очень быстро, но я считаю, что дизайн базы данных будет более полезным с точки зрения масштабирования матриц с точки зрения символов + дат и резервных копий данных.

То, что я до сих пор пробовал по параметрам RDBMS:

А)

- Fields: Symbol, Variable, Date, Value
- Seperate and clustered indices for all but value.
- Data needs to be "melted"/pivoted to a mxn matrix for R (crazy memory inefficient)
- Average query for a normal sample into R: 4-8 minutes

В)

- Each variable in a sperate table.
- Fields: Symbol, Date, Value
- Seperate and clustered indices for all but value.
- Views added to cache common subsets (dunno if it helped at all...)
- Data needs to be "melted"/pivoted to a mxn matrix for R (crazy memory inefficient)
- Average query for a normal sample into R: 3-5 minutes

C). Возможно, здесь была использована база данных, основанная на столбцах.

- Symbols and dates stored seperately and map to row and col numbers only
- Each variable in a seperate table with symbols for rows and dates for columns
- Really bad for where data maps to disk when scaling rows and cols.
- Data already in correct format for R
- Average query for a normal sample into R: 1-3 minutes

По сравнению с приведенной выше базой данных загрузка нагрузки во всей переменной из файлов RData занимает 5 секунд локально и 20 секунд по сети. Все времена базы данных находятся по сети.

Есть ли что-нибудь, что я могу сделать, чтобы маршрут базы данных находился где-то близко к скорости двоичного файла?

Может быть, одна из табличных баз данных nosql - это то, что мне нужно?

Как эта шкала с точки зрения дополнительных символов + дат?

Любая помощь от кого-то, кто имеет дело с чем-то подобным, будет оценена по достоинству.

Обновление: Думаю, что опубликую обновление. В итоге я пошел с предложением Iterator, и теперь данные размещаются в файлах с памятью с большими памятьями, а затем RData для быстрой проверки перетаскивания, а также выводятся в csv и вытаскиваются SQL Server для целей резервного копирования. Любое решение для базы данных слишком медленно используется несколькими пользователями. Кроме того, использование RODBC для SQL-сервера сумасшедшее медленное, но попытка ввода и вывода в R через CSV в SQL и из SQL, и это было нормально, но бессмысленно.

Также для ссылок байт-компиляция метода загрузки для bigmemory действительно влияет. Вот результаты моего теста нагрузки для RData vs bigmemory.

workingDirectory <- "/Users/Hans/92 Speed test/"
require("bigmemory")
require("compiler")
require("rbenchmark")

LoadVariablesInFolder <- function(folder, sedols, dates) {
    filesInFolder <- dir(folder)
    filesToLoad <- filesInFolder[grepl(".*NVAR_.*\\.RData",filesInFolder)]
    filesToLoad <- paste(folder,filesToLoad,sep="/")
    variablesThatWereLoaded <- c()
    for(fToLoad in filesToLoad) {
        loadedVar <- load(fToLoad)
        assign(loadedVar,get(loadedVar)[sedols,dates])
        gc() -> ans
        variablesThatWereLoaded <- c(variablesThatWereLoaded,loadedVar)
        rm(list=c(loadedVar))
    }
    return(variablesThatWereLoaded)
}

cLoadVariablesInFolder <- cmpfun(LoadVariablesInFolder)

BigMLoadVariablesInFolder <- function(folder, sedols, dates) {
    workD <- getwd()
    setwd(folder)
    filesInFolder <- dir(folder)
    filesToLoad <- filesInFolder[grepl(".*NVAR_.*\\.desc",filesInFolder)]
    variablesThatWereLoaded <- c()
    for(fToLoad in filesToLoad) {
        tempVar <- attach.big.matrix(dget(fToLoad))
        loadedVar <- gsub(".*(NVAR_\\d+).*","\\1",fToLoad,perl=TRUE)
        assign(loadedVar,tempVar[sedols,dates])
        variablesThatWereLoaded <- c(variablesThatWereLoaded,loadedVar)
        rm(list=c(loadedVar,"tempVar"))
        gc() -> ans
    }
    setwd(workD)
    return(variablesThatWereLoaded)
}

cBigMLoadVariablesInFolder <- cmpfun(BigMLoadVariablesInFolder)

testCases <- list(
                list(numSedols=1000,numDates=120),
                list(numSedols=5000,numDates=120),
                list(numSedols=50000,numDates=120),
                list(numSedols=1000,numDates=350),
                list(numSedols=5000,numDates=350),
                list(numSedols=50000,numDates=350))

load(paste(workingDirectory,"dates.cache",sep="/"))
load(paste(workingDirectory,"sedols.cache",sep="/"))

for (testCase in testCases) {
    results <- benchmark(LoadVariablesInFolder(folder=workingDirectory,sedols=sedols[1:testCase$numSedols],dates=dates[1:testCase$numDates]),
              cLoadVariablesInFolder(folder=workingDirectory,sedols=sedols[1:testCase$numSedols],dates=dates[1:testCase$numDates]),
              BigMLoadVariablesInFolder(folder=workingDirectory,sedols=sedols[1:testCase$numSedols],dates=dates[1:testCase$numDates]),
              cBigMLoadVariablesInFolder(folder=workingDirectory,sedols=sedols[1:testCase$numSedols],dates=dates[1:testCase$numDates]),
              columns=c("test", "replications","elapsed", "relative"),
              order="relative", replications=3)
    cat("Results for testcase:\n")
    print(testCase)
    print(results)
}

В основном чем меньше подмножество, тем больше получается, потому что вы не тратите время на загрузку во всей матрице. Но загрузка всей матрицы происходит медленнее, чем у RData, я полагаю, что это накладные расходы на преобразование:

# Results for testcase:
# $numSedols
# [1] 1000

# $numDates
# [1] 120

                                                                                                                              # test
# 4 cBigMLoadVariablesInFolder(folder = workingDirectory, sedols = sedols[1:testCase$numSedols], dates = dates[1:testCase$numDates])
# 3  BigMLoadVariablesInFolder(folder = workingDirectory, sedols = sedols[1:testCase$numSedols], dates = dates[1:testCase$numDates])
# 1      LoadVariablesInFolder(folder = workingDirectory, sedols = sedols[1:testCase$numSedols], dates = dates[1:testCase$numDates])
# 2     cLoadVariablesInFolder(folder = workingDirectory, sedols = sedols[1:testCase$numSedols], dates = dates[1:testCase$numDates])
  # replications             elapsed           relative
# 4            3   6.799999999999955  1.000000000000000
# 3            3  14.389999999999986  2.116176470588247
# 1            3 235.639999999999986 34.652941176470819
# 2            3 250.590000000000032 36.851470588235543
# Results for testcase:
# $numSedols
# [1] 5000

# $numDates
# [1] 120

                                                                                                                              # test
# 4 cBigMLoadVariablesInFolder(folder = workingDirectory, sedols = sedols[1:testCase$numSedols], dates = dates[1:testCase$numDates])
# 3  BigMLoadVariablesInFolder(folder = workingDirectory, sedols = sedols[1:testCase$numSedols], dates = dates[1:testCase$numDates])
# 1      LoadVariablesInFolder(folder = workingDirectory, sedols = sedols[1:testCase$numSedols], dates = dates[1:testCase$numDates])
# 2     cLoadVariablesInFolder(folder = workingDirectory, sedols = sedols[1:testCase$numSedols], dates = dates[1:testCase$numDates])
  # replications             elapsed           relative
# 4            3   7.080000000000155  1.000000000000000
# 3            3  32.730000000000018  4.622881355932105
# 1            3 249.389999999999873 35.224576271185654
# 2            3 254.909999999999854 36.004237288134789
# Results for testcase:
# $numSedols
# [1] 50000

# $numDates
# [1] 120

                                                                                                                              # test
# 3  BigMLoadVariablesInFolder(folder = workingDirectory, sedols = sedols[1:testCase$numSedols], dates = dates[1:testCase$numDates])
# 4 cBigMLoadVariablesInFolder(folder = workingDirectory, sedols = sedols[1:testCase$numSedols], dates = dates[1:testCase$numDates])
# 2     cLoadVariablesInFolder(folder = workingDirectory, sedols = sedols[1:testCase$numSedols], dates = dates[1:testCase$numDates])
# 1      LoadVariablesInFolder(folder = workingDirectory, sedols = sedols[1:testCase$numSedols], dates = dates[1:testCase$numDates])
  # replications           elapsed          relative
# 3            3 146.3499999999999 1.000000000000000
# 4            3 148.1799999999998 1.012504270584215
# 2            3 238.3200000000002 1.628425008541171
# 1            3 240.4600000000000 1.643047488896482
# Results for testcase:
# $numSedols
# [1] 1000

# $numDates
# [1] 350

                                                                                                                              # test
# 3  BigMLoadVariablesInFolder(folder = workingDirectory, sedols = sedols[1:testCase$numSedols], dates = dates[1:testCase$numDates])
# 4 cBigMLoadVariablesInFolder(folder = workingDirectory, sedols = sedols[1:testCase$numSedols], dates = dates[1:testCase$numDates])
# 1      LoadVariablesInFolder(folder = workingDirectory, sedols = sedols[1:testCase$numSedols], dates = dates[1:testCase$numDates])
# 2     cLoadVariablesInFolder(folder = workingDirectory, sedols = sedols[1:testCase$numSedols], dates = dates[1:testCase$numDates])
  # replications            elapsed          relative
# 3            3  83.88000000000011 1.000000000000000
# 4            3  91.71000000000004 1.093347639484977
# 1            3 235.69000000000005 2.809847401049115
# 2            3 240.79999999999973 2.870767763471619
# Results for testcase:
# $numSedols
# [1] 5000

# $numDates
# [1] 350

                                                                                                                              # test
# 3  BigMLoadVariablesInFolder(folder = workingDirectory, sedols = sedols[1:testCase$numSedols], dates = dates[1:testCase$numDates])
# 4 cBigMLoadVariablesInFolder(folder = workingDirectory, sedols = sedols[1:testCase$numSedols], dates = dates[1:testCase$numDates])
# 2     cLoadVariablesInFolder(folder = workingDirectory, sedols = sedols[1:testCase$numSedols], dates = dates[1:testCase$numDates])
# 1      LoadVariablesInFolder(folder = workingDirectory, sedols = sedols[1:testCase$numSedols], dates = dates[1:testCase$numDates])
  # replications           elapsed          relative
# 3            3 135.6999999999998 1.000000000000000
# 4            3 155.8900000000003 1.148784082535008
# 2            3 233.3699999999999 1.719749447310245
# 1            3 240.5599999999995 1.772733971997051
# Results for testcase:
# $numSedols
# [1] 50000

# $numDates
# [1] 350

                                                                                                                              # test
# 2     cLoadVariablesInFolder(folder = workingDirectory, sedols = sedols[1:testCase$numSedols], dates = dates[1:testCase$numDates])
# 1      LoadVariablesInFolder(folder = workingDirectory, sedols = sedols[1:testCase$numSedols], dates = dates[1:testCase$numDates])
# 3  BigMLoadVariablesInFolder(folder = workingDirectory, sedols = sedols[1:testCase$numSedols], dates = dates[1:testCase$numDates])
# 4 cBigMLoadVariablesInFolder(folder = workingDirectory, sedols = sedols[1:testCase$numSedols], dates = dates[1:testCase$numDates])
  # replications           elapsed          relative
# 2            3 236.5000000000000 1.000000000000000
# 1            3 237.2100000000000 1.003002114164905
# 3            3 388.2900000000000 1.641818181818182
# 4            3 393.6300000000001 1.664397463002115
4b9b3361

ОТВЕТЫ

Ответ 1

Я бы настоятельно рекомендовал использовать HDF5. Я полагаю, что ваши данные достаточно сложны, что множество файлов bigmemory (т.е. Матриц с отображением памяти) не будет легко удовлетворять ваши потребности (см. Примечание 1), но HDF5 просто не соответствует скорости файлов с отображением памяти. См. этот более длинный ответ на другой вопрос, чтобы понять, как я сравниваю файлы HDF5 и .RDat.

В частности, тот факт, что HDF5 поддерживает произвольный доступ, означает, что вы должны иметь возможность значительно улучшить скорость.

Другой вариант, в зависимости от вашей готовности создавать собственный бинарный формат, заключается в использовании readBin и writeBin, хотя у него нет всех хороших функций, которые имеет HDF5, включая параллельный ввод-вывод, версию информации, переносимости и т.д.

Примечание 1: Если у вас есть только несколько типов для строки, то есть 1 символ, а остальные - числовые, вы можете просто создать 2 матрицы с отображением памяти, одна из которых предназначена для символов, другая для числовых значений. Это позволит вам использовать bigmemory, mwhich, bigtabulate и множество других приятных функций в наборе bigmemory. Я бы приложил разумные усилия, так как это очень простая система для плавной интеграции с большим количеством R-кода: матрице никогда не нужно вводить память, просто какие бы подмножества вам ни понадобились, и многие экземпляры могут одновременно обращаться к тем же файлам. Что еще, легко распараллелить доступ с помощью многоядерных бэкендов для foreach(). Раньше у меня была операция, которая занимала бы около 3 минут на файл .Rdat: около 2 минут на загрузку, около 20 секунд, чтобы подбирать то, что мне нужно, около 10 секунд для анализа и около 30 секунд, чтобы сохранить результаты. После перехода на bigmemory, я собрался примерно на 10 секунд для анализа и около 5-15 секунд ввода-вывода.

Обновление 1: я упустил ff-пакет - это еще один хороший вариант, хотя он намного сложнее, чем bigmemory.

Ответ 2

Возможно, дизайн базы данных TSdbi вдохновляет...

Для решения nosql hdf5 может быть вариантом. Однако я мало что знаю об этом.

Ответ 3

Вы также можете вникать в использование/внутренние функции sqldf. Кажется, что их работа с базами данных довольно хорошо разобралась. На их странице также представлено множество интересных примеров.