Show HN: Work with CSV files using SQL. For data scientists and engineers

99 点作者 tanin将近 4 年前

26 条评论

gaha将近 4 年前

The way I love doing these kinds of things is by literally using three lines of R code:library(sqldf)tab1 = read.csv("file1.csv")sqldf("select * from tab1")sqldf [1] is a library with which you can access R dataframes just like tables in SQL (it is actually using SQLite in the background). I do not have much experience with programming in R itself, but you barely need it. This approach has the advantage that it is very flexible, and you get the power of the (IMHO) best plotting library ggplot with it. Of course, you can also do more complicated stuff like joining multiple dataframes and basically everything else you can do with SQLite, and then store results back in R dataframes. This workflow works if you use an IDE which lets you execute single lines or blocks of codes like R-Studio. Then you also get a nice GUI with it, but there are also plugins for VI/Emacs that work very well.[1] <a href="https://cran.r-project.org/web/packages/sqldf/index.html" rel="nofollow">https://cran.r-project.org/web/packages/sqldf/index.html</a>EDIT: code formatting

评论 #27876370 未加载

评论 #27876483 未加载

cube2222将近 4 年前

Great to see SQL being used more for such use cases! (I really like SQL)This looks nice, especially with the charting!It's good to know though that there are a few similar open source tools already:- q[0] - SQL on CSV and TSV- TextQL[1] - SQL on CSV and TSV- Datasette[2] - Running SQL on various datasets in various formats, by (if I understand it correctly) importing it to sqlite and running the query there. This one's the closest one to the posted project I think, with interactive data exploration and publishing built in.- OctoSQL[3] - SQL on files like CSV, JSON as well as non-file databases, endless streams with temporal SQL extensions and joining everything together in a single query.The performance numbers of this tool however are impressive if they will check out with real data. 1GB in 10 seconds is fast. However, looking at the documentation it looks like most of the heavy lifting is done by SQLite under the hood. From the docs:> Under the hood, Superintendent loads CSVs into a SQLite database.Disclaimer: I'm the author of OctoSQL. Still actively developing a rewrite on the redesign branch.[0]: <a href="https://github.com/harelba/q" rel="nofollow">https://github.com/harelba/q</a>[1]: <a href="https://github.com/dinedal/textql" rel="nofollow">https://github.com/dinedal/textql</a>[2]: <a href="https://datasette.io" rel="nofollow">https://datasette.io</a>[3]: <a href="https://github.com/cube2222/octosql" rel="nofollow">https://github.com/cube2222/octosql</a>

评论 #27872498 未加载

评论 #27874815 未加载

评论 #27874457 未加载

rognjen将近 4 年前

Seems useful but at the same time it's comparison with loading CSV into SQL seems kind of disingenuous since if you're familiar with SQL you're probably familiar with command line enough to load CSV into SQLite (which is free in both meanings)

评论 #27877628 未加载

jonnycomputer将近 4 年前

So its a GUI wrapper around sqlite. I guess I expect data scientists and engineers to be comfortable with command line and scripting, and there are GUIs that will work with sqlite already, so I guess I'm having hard time figuring out the target audience for this.

评论 #27878713 未加载

EMM_386将近 4 年前

This open source tool on Github allows you to quickly import a CSV and write SQL queries against it. The CSV is imported into a SQLite database. It also does a lot more, highly recommended.<a href="https://github.com/sqlitebrowser/sqlitebrowser" rel="nofollow">https://github.com/sqlitebrowser/sqlitebrowser</a>

评论 #27872772 未加载

draegtun将近 4 年前

Perl DBD::CSV module is a classic in this genre (first version is from 1998) - <a href="https://metacpan.org/pod/DBD::CSV" rel="nofollow">https://metacpan.org/pod/DBD::CSV</a>

banana_giraffe将近 4 年前

> A python script loading a 1GB file is likely to take more than 1 minutes on any machine.Since when? I just tested it, and I can process a 1gb CSV file on my machine using the csv module in Python in ~10 seconds. My machine isn't a world record setting machine, either.

评论 #27875977 未加载

sgt将近 4 年前

It seems interesting, but what is the advantage over just \copy in Postgres from a CSV file - or better yet, import into SQLite without even providing the table definition?

评论 #27872748 未加载

评论 #27874757 未加载

评论 #27872684 未加载

hprotagonist将近 4 年前

The datasette author offers this tool for conversion: <a href="https://github.com/simonw/csvs-to-sqlite" rel="nofollow">https://github.com/simonw/csvs-to-sqlite</a>

评论 #27873338 未加载

beckingz将近 4 年前

Cool tool!Panda SQL is great for doing SQL on pandas dataframes. <a href="https://pypi.org/project/pandasql/" rel="nofollow">https://pypi.org/project/pandasql/</a>

评论 #27876353 未加载

marto1将近 4 年前

Really cool and smooth! One killer feature here will be auto translating to pandas for people to copy over snippets to their Jupyter notebooks.You don't want to know how many times I've been asked to do exactly that.Other than that one can do that (somewhat clumsily) with postgres using file_fdw like:CREATE FOREIGN TABLE mytable ( datetime timestamp(3) with time zone, name text, score integer, ) SERVER myserver OPTIONS ( filename 'data/file1.csv', format 'csv' );and then query as usual. More docs here: <a href="https://www.postgresql.org/docs/13/file-fdw.html" rel="nofollow">https://www.postgresql.org/docs/13/file-fdw.html</a>

hodgesrm将近 4 年前

You can query CSV directly without a DBMS backend using clickhouse-local. [1] It's often used to clean data exactly as this article describes. You can also load CSV very easily into tables.Superintendent.app seems to bring automatic schema definition to the table. We've discussed adding this to ClickHouse. Would be a great PR if anyone is interested.[1] <a href="https://altinity.com/blog/2019/6/11/clickhouse-local-the-power-of-clickhouse-sql-in-a-single-command" rel="nofollow">https://altinity.com/blog/2019/6/11/clickhouse-local-the-pow...</a>(This note brought to you by the ClickHouse evangelization task force.)

评论 #27875938 未加载

评论 #27877687 未加载

aphrax将近 4 年前

This area interests me but being completely honest there are a few off-putting grammatical/spelling errors. Might be worth getting them cleaned up..

评论 #27874719 未加载

eatonphil将近 4 年前

Very neat! I'm also working on an (open-source/core) app for running SQL against any data (copy-pasted stuff, data files, logs, HTTP responses, other SQL requests) and being able to script and graph as well.A difference might be that the goal I have for DataStation is to aid more in data exploration (but not being limited to smaller datasets like this site points out Google Sheets and Excel are). Some optimizations like streaming all data (and streaming through scripts) are currently out of scope and that will definitely limit the scale DataStation can handle.It's primarily to help out the case where you have a bunch of different data and you want to join/filter/group and graph the result for some business/product/engineering executive team.<a href="https://datastation.multiprocess.io/" rel="nofollow">https://datastation.multiprocess.io/</a>

jdnier将近 4 年前

If your work is more OLAP focused, DuckDb has good tools for loading csv files. <a href="https://duckdb.org/docs/data/csv" rel="nofollow">https://duckdb.org/docs/data/csv</a>DuckDB is like a columnar SQLite. It also has great support for Pandas DataFrames.

kmerroll将近 4 年前

Loading and running SQL on structured text files (.CSV) is definitely an underserved data tools niche, but this statement threw me: "No more custom and slow Python script. No more vlookup, no more pivot tables." Wow, I'll keep my slow Python/Pandas script thank you.

cube00将近 4 年前

No license keys required with H2 <a href="http://www.h2database.com/html/tutorial.html#csv" rel="nofollow">http://www.h2database.com/html/tutorial.html#csv</a>

robbiejs将近 4 年前

Looks really nice. In what language is the app itself written? If it's web app, I'm curious to know what table component you are using.Also, for anyone looking to edit simple csv files in an excel-like matter, please check <a href="https://editcsvonline.com" rel="nofollow">https://editcsvonline.com</a>, a free tool that I made, powered by DataGridXL (which I also made).

adam_gyroscope将近 4 年前

You can do this with bit.io, although it’s not local; import your csv (drag and drop or browse to upload) and get a Postgres database in the cloud. Full disclosure I’m a founder at bit.io.I love to see anything that makes it easier to use data!

gigatexal将近 4 年前

I do this already with sqlite3

refactor_master将近 4 年前

If I open a CSV with pandas in debug mode I’m also pretty much free to play with whatever transformations I want, without being restricted by SQL syntax. Saved as a parquet, reloads are also pretty much instantaneous.

评论 #27874766 未加载

_1tan将近 4 年前

Is this similar to <a href="https://github.com/harelba/q" rel="nofollow">https://github.com/harelba/q</a> ?

chrisweekly将近 4 年前

This reminds me of <a href="https://lnav.org" rel="nofollow">https://lnav.org</a>

mjirv将近 4 年前

I like that this lets you join CSVs, which a lot of tools in this space don’t.

评论 #27876431 未加载

dantetheinferno将近 4 年前

Why isn't this opensourced?

评论 #27880396 未加载

toyg将近 4 年前

I'm old enough to know I should give a pass to anything "free while in beta"...