'How to import a .sql file into DuckDB database?
I'm exploring DuckDB for one of my project.
Here I have a sample Database file downloaded from https://www.wiley.com/en-us/SQL+for+Data+Scientists%3A+A+Beginner%27s+Guide+for+Building+Datasets+for+Analysis-p-9781119669364
I'm trying to import FarmersMarketDatabase into my DuckDB database.
con.execute("IMPORT DATABASE 'FarmersMarketDatabase'")
It throws out an error as:
RuntimeError: IO Error: Cannot open file "FarmersMarketDatabase\schema.sql": The system cannot find the path specified.
How to load the databases into DuckDB?
Solution 1:[1]
I am not sure if you can load .sql file into DuckDB directly. Maybe you can first export sqlite database to CSV file(s). Then load CSV file(s) into DuckDB: https://duckdb.org/docs/data/csv.
Solution 2:[2]
You may want to install the related extension first:
https://github.com/duckdblabs/sqlitescanner
The import/export feature seems to support DuckDB format only.
Solution 3:[3]
As I couldn’t edit my answer above:
I had a brief look into the download you referred to. According to the screenshots included in the zip-download it seems to be a MySQL database dump file (as the tool shown in the screenshots is MySQL Workbench).
DuckDB import/export function supports its own structure and format of a dump.
With the above mentioned extension duckDB can access SQLite database files directly. With another extension PostgreSQL is also supported, but currently no support of MySQL.
In Python you could use Mysqldump to access the file and save the tables as dataframes.
DuckDB can query such dataframes directly.
If you prefer, with a GUI such as dbeaver, you can also access the MySQL dump file and copy the tables to a duckDB database file.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Haojin |
| Solution 2 | Anonymous |
| Solution 3 | Anonymous |