type recognition, *creating* tables from CSVs
Hi, I asked a question on DBA of StackExchange about MonetDB, but as there is not even a monetdb tag in use, maybe it wasn't the right forum for it. Please have a look over there, or I can repeat it below: http://dba.stackexchange.com/q/65126/38399 I am confused whether you can copy records into a *new* table in MonetDB. The official documentation seems[1] to talk only about copying into existing tables. I have quite a large universe of billions of records in dozens of tables, and I would much prefer a solution that inferred the type from the CSV itself, and the column names from the first rows. A StackOverflow answer[2] seems to work for SQL Server 2000 with `OpenRowset`, though even they are not concerned about data-type inferencing. Another answer[3] here on DBA recommends `BCP` before calling `BULK INSERT` in Server 2000, neither of which is discussed in the MonetDB documentation. Other answers[4] also suggest that this is impossible in MySQL without external scripts. The background of my issues are that I realized that managing my raw data (which came in text files from government agencies, with scarce documentation) should be separate from the front-end of my analysis still kept in Stata. This is similar to what has been achieved with MonetDB.R (e.g. hannes.muehleisen.org/SSDBM2013-databases-and-statistics.pdf), though probably limited to an odbc link (so missing some of the real benefits coming from merging and calculations done in MonetDB). That said, this is the only sign of a Stata and MonetDB link I could find: http://www.stata.com/statalist/archive/2012-08/msg01363.html Disclaimer: I am an SQL and MonetDB newbie, but thanks for not letting me miss the obvious. [1]: https://www.monetdb.org/Documentation/Manuals/SQLreference/CopyInto [2]: http://stackoverflow.com/a/10421034/938408 [3]: http://dba.stackexchange.com/a/22763/38399 [4]: http://dba.stackexchange.com/a/61969/38399
Hi, AFAIK the tables must exist. The documentations is here: https://www.monetdb.org/Documentation/Manuals/SQLreference/CopyInto Slightly helpful could be to use KETTLE ETL Tool (http://community.pentaho.com/projects/data-integration/) which is free. You can create a process for loading the CSV files. When you create a transformation using Monet Bulk Loader, there is an option (button SQL) to create table based on incoming fields from a CSV file. Let me know if it sounds interesting so I could elaborate further. :) Hope that helps. Radovan On 05/14/2014 09:06 PM, László Sándor wrote:
Hi, I asked a question on DBA of StackExchange about MonetDB, but as there is not even a monetdb tag in use, maybe it wasn't the right forum for it. Please have a look over there, or I can repeat it below: http://dba.stackexchange.com/q/65126/38399
I am confused whether you can copy records into a *new* table in MonetDB.
The official documentation seems[1] to talk only about copying into existing tables. I have quite a large universe of billions of records in dozens of tables, and I would much prefer a solution that inferred the type from the CSV itself, and the column names from the first rows.
A StackOverflow answer[2] seems to work for SQL Server 2000 with `OpenRowset`, though even they are not concerned about data-type inferencing.
Another answer[3] here on DBA recommends `BCP` before calling `BULK INSERT` in Server 2000, neither of which is discussed in the MonetDB documentation.
Other answers[4] also suggest that this is impossible in MySQL without external scripts.
The background of my issues are that I realized that managing my raw data (which came in text files from government agencies, with scarce documentation) should be separate from the front-end of my analysis still kept in Stata. This is similar to what has been achieved with MonetDB.R (e.g. hannes.muehleisen.org/SSDBM2013-databases-and-statistics.pdf http://hannes.muehleisen.org/SSDBM2013-databases-and-statistics.pdf), though probably limited to an odbc link (so missing some of the real benefits coming from merging and calculations done in MonetDB). That said, this is the only sign of a Stata and MonetDB link I could find: http://www.stata.com/statalist/archive/2012-08/msg01363.html
Disclaimer: I am an SQL and MonetDB newbie, but thanks for not letting me miss the obvious.
[1]: https://www.monetdb.org/Documentation/Manuals/SQLreference/CopyInto [2]: http://stackoverflow.com/a/10421034/938408 [3]: http://dba.stackexchange.com/a/22763/38399 [4]: http://dba.stackexchange.com/a/61969/38399
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Thanks, Radovan — I think I will try Hannes's importer Python script first, but it's good to know about Kettle! http://dba.stackexchange.com/a/65174/38399 Laszlo On Thu, May 15, 2014 at 8:13 AM, Radovan Bičiště < radovan.biciste@ceosdata.com> wrote:
Hi, AFAIK the tables must exist. The documentations is here: https://www.monetdb.org/Documentation/Manuals/ SQLreference/CopyInto
Slightly helpful could be to use KETTLE ETL Tool ( http://community.pentaho.com/projects/data-integration/) which is free. You can create a process for loading the CSV files. When you create a transformation using Monet Bulk Loader, there is an option (button SQL) to create table based on incoming fields from a CSV file. Let me know if it sounds interesting so I could elaborate further. :)
Hope that helps.
Radovan
On 05/14/2014 09:06 PM, László Sándor wrote:
Hi, I asked a question on DBA of StackExchange about MonetDB, but as there is not even a monetdb tag in use, maybe it wasn't the right forum for it. Please have a look over there, or I can repeat it below: http://dba.stackexchange.com/q/65126/38399
I am confused whether you can copy records into a *new* table in MonetDB.
The official documentation seems[1] to talk only about copying into existing tables. I have quite a large universe of billions of records in dozens of tables, and I would much prefer a solution that inferred the type from the CSV itself, and the column names from the first rows.
A StackOverflow answer[2] seems to work for SQL Server 2000 with `OpenRowset`, though even they are not concerned about data-type inferencing.
Another answer[3] here on DBA recommends `BCP` before calling `BULK INSERT` in Server 2000, neither of which is discussed in the MonetDB documentation.
Other answers[4] also suggest that this is impossible in MySQL without external scripts.
The background of my issues are that I realized that managing my raw data (which came in text files from government agencies, with scarce documentation) should be separate from the front-end of my analysis still kept in Stata. This is similar to what has been achieved with MonetDB.R (e.g. hannes.muehleisen.org/SSDBM2013-databases-and-statistics.pdf http://hannes.muehleisen.org/SSDBM2013-databases-and-statistics.pdf),
though probably limited to an odbc link (so missing some of the real benefits coming from merging and calculations done in MonetDB). That said, this is the only sign of a Stata and MonetDB link I could find: http://www.stata.com/statalist/archive/2012-08/msg01363.html
Disclaimer: I am an SQL and MonetDB newbie, but thanks for not letting me miss the obvious.
[1]: https://www.monetdb.org/Documentation/Manuals/ SQLreference/CopyInto [2]: http://stackoverflow.com/a/10421034/938408 [3]: http://dba.stackexchange.com/a/22763/38399 [4]: http://dba.stackexchange.com/a/61969/38399
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Hello, Please I'm looking for an example of usage JSON data type and JAQL (Jacqueline). I have default branch compiled, running and created a table with JSON. I access the database through JDBC client (Workbench/J). I inserted a row into the table with JSON column. How can I query the column containing JSON value using JAQL? I might be totally missing the point. Thank you for any hints. Radovan
Hello, Jacqueline was/is an experimental branch in the Jan14 MonetDB distribution, which is scheduled to be deprecated with the next feature release. Given the state of its implementation, you would not get far in querying the JAQL store (which actually has its own multi-column representation). regards, Martin On 15/05/14 16:12, Radovan Bičiště wrote:
Hello, Please I'm looking for an example of usage JSON data type and JAQL (Jacqueline). I have default branch compiled, running and created a table with JSON. I access the database through JDBC client (Workbench/J). I inserted a row into the table with JSON column. How can I query the column containing JSON value using JAQL? I might be totally missing the point. Thank you for any hints.
Radovan _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Thank you for the clarification. Radovan On 05/15/2014 04:16 PM, Martin Kersten wrote:
Hello,
Jacqueline was/is an experimental branch in the Jan14 MonetDB distribution, which is scheduled to be deprecated with the next feature release. Given the state of its implementation, you would not get far in querying the JAQL store (which actually has its own multi-column representation).
regards, Martin
On 15/05/14 16:12, Radovan Bičiště wrote:
Hello, Please I'm looking for an example of usage JSON data type and JAQL (Jacqueline). I have default branch compiled, running and created a table with JSON. I access the database through JDBC client (Workbench/J). I inserted a row into the table with JSON column. How can I query the column containing JSON value using JAQL? I might be totally missing the point. Thank you for any hints.
Radovan
participants (3)
-
László Sándor -
Martin Kersten -
Radovan Bičiště