The Canada census data in a SQLite file; advice appreciated
This is niche, I'll admit. I needed to look through the latest census data, but it was exported as multiple multi-gigabyte bespoke latin1-encoded CSV files. Pandas, Polars, and SQLite's CSV import tool weren't much help, so I shelved the project until recently, when I started taking a SQLite course online. I picked it up again, normalized the data, and now there's a database that can be queried through a SQL view that matches the headings in the original CSVs. I'm proud of the script I created to export the data, as well as automatically compress the artifact, make the diagrams and…
What it does
In the maker’s words, at launch
This is niche, I'll admit. I needed to look through the latest census data, but it was exported as multiple multi-gigabyte bespoke latin1-encoded CSV files. Pandas, Polars, and SQLite's CSV import tool weren't much help, so I shelved the project until recently, when I started taking a SQLite course online. I picked it up again, normalized the data, and now there's a database that can be queried through a SQL view that matches the headings in the original CSVs. I'm proud of the script I created to export the data, as well as automatically compress the artifact, make the diagrams and checksums, etc. This is my first time building up a big database, does my schema seem sane? I've been considering switching the counts from REALs to TEXT, since then SQLite's decimal extension can do exact calculations, but considering there's only one or two places after the decimal points in the data, I'm not sure if it's worth it space-wise.
Does the same job
all alternatives →- CECensus – Export for data warehouses2020 · ▲34
Hello Hacker News! We're a team of YC founders (Meldium W13, Draft S11, TapEngage S11) launching something new (https://www.getcensus.com). How many times has your business team asked you to generate yet another CSV file, write a ”quick report” in SQL, or send some custom data to a terrible API (looking at you Marketo)? We’ve built a product that connects directly to your data warehouse and syncs into apps like Salesforce, Customer.io and even Google Sheets. In fact, your business teams won’t even need to rely on engineering to manage all these pipelines. The tech stack for…
- WWWork with CSV files using SQL. For data scientists and engineers2021 · superintendent.app · ▲99
- SQSQLite query inside a Bash function2021 · ▲134
Part of the workflow for building my website is the generation of a table in tab-separated column format (.tsv). The source data is found in four other .tsv files. I use an SQLite query to perform a 4-way join and write out the new table. For convenience, I wrote a script that encapsulates the query inside a Bash function. The example below illustrates this technique. repertoire() { pushd $CMM_SOURCES/_data sqlite3 <<EOS .headers on .mode tabs .import category.tsv category .import composition.tsv composition .import concert.tsv concert .import program.tsv program .once repertoire.tsv…
- SSSPyQL – SQL with Python in the middle2022 · github.com · ▲105
SPyQL (https://github.com/dcmoura/spyql) is SQL with Python in the middle, an open-source project fully written in Python for making command-line data processing more intuitive, readable and powerful. Try mixing in the same pot: a SQL SELECT for providing the structure, Python expressions for defining transformations and conditions, the essence of awk as a data-processing language, and the JSON handling capabilities of jq. How does a SPyQL query looks like? $ spyql “ IMPORT pendulum AS p SELECT (p.now() - p.from_timestamp(purchase_ts)).in_days() AS days_ago, sum_agg(price…
More dev tools this month
the category →
Open-source GTM skills for technical founders
Dev tools · 29d ago · gtmcofounder.com
OpenTrailPaper is open-source bike computer firmware for the LilyGO T5S3 4.7" E-Paper PRO. It supports offline maps, GPX routes, FIT recording and Bluetooth sensors.
Dev tools · 2d ago · opentrailpaper.com
