4
$\begingroup$

I am exporting data from an SQL database and importing it into R. This is a two step process since I first (automatically) download the data to a hard drive and then import the file with R.

Currently, I am using csv files to save the data. Everybody supports csv. But csv does not support type information. This makes it sometimes cumbersome to load a csv file because I must check all the column types. This seems unnecessary because the SQL database already specifies the types of the columns.

I want to know if there is a broadly accepted file format to save data that also specifies the type of the columns.

Currently I am working with SQL databases, FME ETL'ing and R but I think this is an issue for every data tranfer.

Improve this question
$\endgroup$
6
  • $\begingroup$ XML and HDF5 might be candidates. $\endgroup$
    – aventurin
    Aug 25, 2016 at 12:19
  • 1
    $\begingroup$ Can't you use R's database drivers and read what you want straight from it? What kind of server are you talking to? MySQL? Postgres? MS-SQL? There's loads of info out there on getting R to talk to them. support.rstudio.com/hc/en-us/articles/… $\endgroup$
    – Spacedman
    Aug 26, 2016 at 7:27
  • $\begingroup$ The problem is that I cannot use R to connect to the database. It is protected such that it can only be accessed within the system and R is not on the system. So I need to transfer it to my laptop. $\endgroup$
    – Pieter
    Aug 26, 2016 at 11:57
  • $\begingroup$ Btw, it's a pilot project. I get that this is not a definite solution. But the question does not need to be about a database. $\endgroup$
    – Pieter
    Aug 26, 2016 at 11:58
  • $\begingroup$ What can you do on the database server or with a database connection? Can you run Python or any other language on the database server just to connect and create a more specialised file which you can transfer to your workstation? Can you dump the data as SQL? Clearly the remote DB is the sticking point in terms of functionality, so we really need to know what capabilities it has. If all you can do is write CSVs, then that's pretty much it, unless you can write the schema metadata as a CSV and parse that... $\endgroup$
    – Spacedman
    Aug 26, 2016 at 13:45

2 Answers 2

Reset to default
1
$\begingroup$

I think it depends on your requirements. Read/Write, Sparse/Nonsparse,...? There are many alternatives.

Really common is SQLite, the "most widely deployed and used database engine", a small relational database, these days used behind-the-scenes by many open source and commercial software packages with data storage needs (e.g., Adobe Lightroom, Mozilla Firefox).

From the top of my head:

If you work with R and python:

The feather software was designed for fast data-frame serialization. It is currently available for R and python. Two R and Python authorities designed it in a collaboration. It's built on top of "Apache Arrow" and/or "protocol buffers", it's fast for reading, but it's in alpha state.

There are some serialization formats available from the XML community. You can store complex webs of objects in these formats.

There is JSON and JSON-schema.

If your tables are sparse, there is, for instance, "sparse ARFF" format (in little use, though). There must be others (I have to look this up myself)

Improve this answer
$\endgroup$
2
$\begingroup$

Parquet and Avro both support data types (strings, integers, floats, etc). These are the primary file types used for "big data" projects, although your data doesn't have to be big. Apache Spark is able to read both with ease. Having said that, I'm a big fan of keeping data in a SQL database (e.g., MySQL or Postgres) because that is what they are built for. If you can't re-use the database you're pulling from, could you make your own database locally or on a separate server? I would try using a relational database until your data exceeds 50 GB (an arbitrarily "somewhat large" size), and then I would use Avro or Parquet.

Improve this answer
$\endgroup$
6
  • $\begingroup$ I don't see how this helps, unless there's an R interface to Spark/Parquet/Avro that doesn't involve dumping the data to CSV first? Let's see.. oh yes.. stackoverflow.com/questions/30402253/… but then there's interfaces for most SQL db so why complicate life with a new data base system? $\endgroup$
    – Spacedman
    Aug 26, 2016 at 7:30
  • $\begingroup$ @Spacedman Yes, that's why I said, "I would try using a relational database until your data exceeds 50 GB". OP said "I am exporting data from an SQL database and importing it into R. ... [and] I am using csv files to save the data" so I figured that he had already considered making a new table on the existing RDBMS and had to save the data elsewhere for some unspecified reason. $\endgroup$
    – Ryan Zotti
    Aug 26, 2016 at 13:25
  • $\begingroup$ So you're proposing an SQL dump of the data from the secure DB (a file of basically CREATE TABLE and INSERT INTO statements), transfer that to workstation, import into similar DB system running locally? That would work, and preserve types via the DB schema. $\endgroup$
    – Spacedman
    Aug 26, 2016 at 13:44
  • $\begingroup$ @Spacedman Yes, that's right $\endgroup$
    – Ryan Zotti
    Aug 26, 2016 at 15:56
  • $\begingroup$ How does these two solutions compare to hdf5? $\endgroup$
    – Pieter
    Aug 29, 2016 at 8:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.