DuckDB Files - Basics

[ducklingQuackQuack]

For context, the devs at work pointed me to duckdb as Excel wasn't cutting the mustard anymore.

This is all very very foreign to me. I do not have a computer science background. I'm just some guy swimming in garbage data and an overheating laptop.

I had tried Qstudio, which was not appropriate. Harlequin is much cleaner - and easier to use. I'm at a loss as to saving a "persistance database" using Harlequin. With Qstudio, it was quite simple to do so. And it saved a .duckdb file with a nice little duck icon on my C drive.

With Harlequin, I can use the EXPORT DATABASE, pointed at a directory, and it generates a series of files there. It functions much the same way, but I'm very much confused as to why there's two different approaches.

As this is all so foreign to me, I'm trying to reduce the components I'm interacting with as I ramp up my knowledge over the coming months.

Thanks!

[tconbeer]

Glad you opened this discussion - I started my journey with Data about 18 years ago in nearly the same way.

The short answer is, you can invoke Harlequin with a database filename, and Harlequin will create the persistent database for you. From your shell (terminal or command-line), assuming you are in a directory with your source data files and where you want to save the DuckDB database file:

harlequin my_database_name.duckdb -f .

Then inside Harlequin, you can execute any DDL queries to create tables in your new database:

create table my_first_db_table as (
  select * from 'my_csv_file.csv'
)

Figured this would be useful for others, also, so I recorded a quick demo video, if that helps!
https://www.tella.tv/video/turning-csv-to-duckdb-4sen

Good luck building some new skills!

[tconbeer]

p.s., the export database command does something slightly different -- it takes a database (either persistent or in-memory) and exports it as a series of CSV files and 2 SQL files that give DuckDB the code to rebuild the database from the CSV files. Here's the docs on export database: https://duckdb.org/docs/sql/statements/export.html

[ducklingQuackQuack]

Oustanding! Thank you. I'm able to create a persistent db. I very much appreciate the video. It's infinitely easier to follow along, as context is limited in the written form.

---

This may be 'scope creep' and pertaining to my personal configuration, if so, we can let sleeping ducks lie. I don't want to abuse.

That being said, short-term I'll need to "point" harlequin and my efforts to Sharepoint (yes it's slow, but that's okay at this stage). My colleague and I will need to easily access the each other's source files & duckdb files.

Here's how I created the test db file using windows PowerShell:

PS C:\Users\myname\pipx\venvs\harlequin\Scripts> ./harlequin -f "C:\Users\myname\OneDrive - mycompany\Desktop\HarlequinTest" TedDB.duckdb

Interestingly, two things happened:

1. In the query editor, it printed "memory". Maybe the logic is that the db isn't "persistent" until I load a pre-existing .duckdb file?

2. Harlequin will correctly read my sample xlsx on my desktop, though when I close Harlequin, TedDB.duckdb was saved in the directory where Harlequin.exe is located - and not the directory I specified.

Given my recent hurdles in removing the comma separating thousands (tl;dr regional context makes it confusing), I presume there's an explicit 'command' that I'm fumbling or omitting and the computer is thus behaving in this 'unintended manner'.

Again, this is veering into scope creep territory and may be veering from the realm of Harlequin into muddied waters of Windows. That's fine, I can make janky manual business processes as a workaround for the time being!

[tconbeer]

The argument is technically a path to the database file. This will work:

PS C:\Users\myname\pipx\venvs\harlequin\Scripts> ./harlequin -f "C:\Users\myname\OneDrive - mycompany\Desktop\HarlequinTest" "C:\Users\myname\OneDrive - mycompany\Desktop\HarlequinTest\TedDB.duckdb"

[tconbeer]

Also, re: removing commas, you can pass options to DuckDB to change its defaults for delimiters and quoting. See this page: https://duckdb.org/docs/data/csv/overview.html