Openrefine: Feature Request: Export SqlDump

[tfmorris]

_Original author: mcnamara.[email protected] (November 15, 2010 01:02:33)_

Adding an ability to export the project as a SQL dump file[1], possibly in MySQL's format, would be beneficial.

[1] http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

_Original issue: http://code.google.com/p/google-refine/issues/detail?id=205_

[tfmorris]

_From [email protected] on November 16, 2010 12:58:30:_
Can this be supported by a template in the templating exporter? I'd imagine beside the default json template, we can also generate a wikitext template, and perhaps this sql dump file.

[tfmorris]

_From thadguidry on November 16, 2010 15:15:11:_
Should be able to handle with templating exporter. It will just be a text file anyway with a single CREATE statement for the table (per project?). If folks need more than that, then they just use their database's restore options or other converter programs on the basic .sql text file generated.

2 options that I could see useful for folks:

1. Supporting COMMENTS statement
2. A checkbox option to include exporting the data & schema or just the schema (for testing)

[tfmorris]

_From [email protected] on December 22, 2010 09:36:23:_
If GR can connect and write to a SQL database by using a JDBC bridge, it wouldn't be necessary to dump the results as a SQL file.

Similar to issue: http://code.google.com/p/google-refine/issues/detail?id=12

[thadguidry]

1277 is being worked on to handle this issue now ! :)

1277 will also handle the case of issue #12 MySQL Support

[wetneb]

I believe this also connects with metadata support as we would need column types to export the project as a SQL table. #778 #1096

[stevevance]

@wetneb I would like to be able to export all columns as "text" and then deal with them later in PostgreSQL/MySQL.

[thadguidry]

@stevevance we already have export as CSV which you can load into any database. This feature was to connect to a database and perform a CREATE TABLE with the selected columns in OpenRefine. Is there something more than that feature you need ? Let us know how you envision it working.

[stevevance]

I would like OpenRefine to export SQL with two options: (1) the CREATE statement includes a datatype for each column ("text", "varchar", "int", "bigint") based on how OpenRefine reads the data in that column; (2) the CREATE statement uses a generic "text" datatype for every column.

For large datasets, with many columns, it's very time consuming to write or rewrite a CREATE statement that matches the (2) option above.

I normally use a GUI program to import CSV files called Navicat, instead of the COPY command in PostgreSQL.

[thadguidry]

@stevevance Right, so you'd like a nice dialog that let's you set all those column options, AND even an option that says "Set the selected Datatype on all columns" :)

[stevevance]

@thadguidry I found a program that will write CREATE statements! It's csvsql, which is part of csvkit: http://csvkit.readthedocs.io/en/0.9.1/scripts/csvsql.html

[thadguidry]

@tcbuzor I think it makes sense to have the dialog expose a dropdown for the Database vendor, and then another showing the enumerated list of Datatypes supported by that Database vendor, along with the option to check to make ALL selected OpenRefine columns to be set as the users selected SQL Datatype. We can reuse part of our existing export dialog that currently exposes the individual Columns options.

[tcbuzor]

is someone working on this?

[wetneb]

@tcbuzor the floor is yours!

[tcbuzor]

@thadguidry , @wetneb should this generate a CREATE statement as well or just an INSERT statement?

[thadguidry]

@tcbuzor CREATE statement, and truncated to first 30 bytes (whitespace collapsed) of the OpenRefine Project name.

[tcbuzor]

@thadguidry Why the first 30 bytes? The user should be allowed to change the table name as he/she chooses. We also need to allow the user specify the column type and sizes...

[thadguidry]

@tcbuzor Oracle < 12.1 has a table name limit of 30 bytes.

[tcbuzor]

The column names also have a limit right?

[thadguidry]

@tcbuzor Yeap, column name max length is 30 bytes if its Oracle < 12.1

[tcbuzor]

Hi All,
What do you guys think about the proposed UI for database export attached here:

[thadguidry]

@tcbuzor I would also add text somewhere explaining what goes into the input boxes.

[tcbuzor]

I'll take a look and implement something similar.

On Tue, Mar 20, 2018 at 10:40 PM, Thad Guidry [e-mail]
wrote:

@tcbuzor https://github.com/tcbuzor I would also add hover text
explaining what goes into the input boxes when you click into like we have
on Wikidata Schema alignment that @wetneb https://github.com/wetneb has
on his wikidata-extension branch.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/OpenRefine/OpenRefine/issues/205#issuecomment-374826362,
or mute the thread
https://github.com/notifications/unsubscribe-auth/Af2f5W06k28V6VXGGtJ1bpWnHgvl_vxyks5tgcurgaJpZM4AM1M3
.

[tcbuzor]

Download tab below:

[thadguidry]

@tcbuzor Should that say instead "Include Schema" ? Wondering if "Include Structure" might confuse folks, because its kind of an Oracle naming convention, rather than universal. Also re-read my comment above, I've updated it (don't use hover text, just text somewhere "input max values into boxes as necessary" or some such)

[stevevance]

I prefer to use TEXT instead of VARCHAR, and I would also like to see a NUMERIC (decimal/float) option.

[thadguidry]

We delivered the basic SQL Export functionality in OpenRefine 3.0 ( Thanks @tcbuzor ! )
Additional Features can be requested by opening a New Issue.