Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Ask HN: What tools do you use for data munging and merging?

news.ycombinator.com

Post reply on HN
Print view View original

21–30 of 74 posts

Re: Ask HN: What tools do you use for data munging and merging?

#21

Post by hn12345 »

Looks like you're looking for ETL solutions. It's funny reading some of the replies here, you can tell who's coming from a more BI background compared to a software engineering background.

Anyways, I think Alteryx does this job really well. It's great for ETL that the average Joe can pick up and learn very quickly. Sure, the UX could be improved, but it's miles better than AWS' Data Pipeline and other tools. It is a bit costly like another user mentioned, but well worth it IMO. Tableau has introduced some new products the past year or two to compete in this space as well, so if you use Tableau for reporting, look into their new offerings. Dell introduced an ETL tool a few years ago called Boomi. It had some promise a few years ago and might be something to consider. I have no idea what it costs though. Another option is SSIS if the data is eventually going to live in SQL Server or some Microsoft database.

Ultimately, I would consider options based on your target database/environment.

Re: Ask HN: What tools do you use for data munging and merging?

#23

Post by clavalle »

Sublime text isn't as featured as Talend tools but it does what it does well and quickly and stays out of my way. Great for loading up a text file, scrolling around, and doing quick find/replaces.

Shell scripts using mostly sed and awk go a very, very long way when I want something repeatable or I'm dealing with a lot of data. And, when that starts getting heavy or long and I need more structure, python.

GUIs just get in the way in this space. By necessity, they need to be opinionated because there's only so much screen to get around in. They also tend to create artifacts that aren't quick to understand later and are locked into some particular product or way of doing things.

Re: Ask HN: What tools do you use for data munging and merging?

#24

Post by hatmatrix »

cube2222 wrote: post #17

A project I'm using, which I'm also one of the authors of, is OctoSQL[1]. It allows you to transform and join data from different data sources (including databases like mysql, postgres, redis, and CSV or JSON files, more to come) using a familiar interface - plain SQL. Other than that, I like Exploratory[2]. And for nested JSON data I use another project of mine, jql[3] - an easier to use (in my opinion) lispy altern…

Interesting project and I like the name. It seems that the underlying relationship in each data source has to be relational though (even for JSON files) - is that a fundamental limitation of using SQL as the glue language, or are there plans to integrate hierarchical models of data in some form as well?

Re: Ask HN: What tools do you use for data munging and merging?

#25

Post by psv1 »

JPKab wrote: post #20
psv1 wrote: post #11

Earlier quoted context omitted.

Problems with Alteryx come when you need to share a workflow with other people, or need any functionality outside of the basic tools, or need a loop, or need good documentation, or your internet is down which completely blocks this otherwise-desktop application, or need compatibility with an older version of Alteryx... I could go on but my point is that beyond the most basic tasks the functionality of Alteryx is limi…

The internet being down doesn't block Alteryx at all. I routinely use it without internet. Sharing of workflows hasn't been an issue either. Understood on the loop part. You have to think on terms of vectorization for that, or use what they call batch macros.

The connection would prevent it from starting up for me because it verifies the license each time you start the program.

Neither vectorisation, nor batch macros (nor iterative macros) allow you to run the same set of commands until a condition is met in Alteryx.

Re: Ask HN: What tools do you use for data munging and merging?

#26

Post by cube2222 »

hatmatrix wrote: post #24
cube2222 wrote: post #17

A project I'm using, which I'm also one of the authors of, is OctoSQL[1]. It allows you to transform and join data from different data sources (including databases like mysql, postgres, redis, and CSV or JSON files, more to come) using a familiar interface - plain SQL. Other than that, I like Exploratory[2]. And for nested JSON data I use another project of mine, jql[3] - an easier to use (in my opinion) lispy altern…

Interesting project and I like the name. It seems that the underlying relationship in each data source has to be relational though (even for JSON files) - is that a fundamental limitation of using SQL as the glue language, or are there plans to integrate hierarchical models of data in some form as well?

If you mean hierarchical data querying (like nested json, or json in sql rows), then that's very high on our radar.

Re: Ask HN: What tools do you use for data munging and merging?

#28

Post by westonsankey »

I try to avoid GUI-based tools as they generally don't lend themselves well to version control, code reuse, etc. There are several orchestration tools that allow you to create arbitrarily complex ETL pipelines using SQL, Python, Spark, etc. Here are links to a few:

http://airflow.apache.org/

https://www.prefect.io/

https://dagster.readthedocs.io/en/0.6.7/

https://www.getdbt.com/

Re: Ask HN: What tools do you use for data munging and merging?

#29

Post by intrepidhero »

hprotagonist wrote: post #15

The python ecosystem is really good here. I do a lot of exploratory coding in ipython, though the threshold for "switch to a real editor and run git init and poetry new" is pretty low. Want to munge CSV? stdlib, or pandas( https://pandas.pydata.org/pandas-docs/stable/ ) Want to munge JSON? stdlib. Want to munge a database? pandas, stdlib, anything that speaks sqlalchemy Want to validate your json/sql/CSV/whatever and…

Sweet. Thanks for the links to seaborn and plotnine. I hadn't seen those before.

I do of automation with csv, yaml, openpyxl, and jinja2. Wrapped in a simple GUI (Qt, Gtk, or Tk, pick your poison) so my non-programmer colleagues can quickly run it and get a PDF report, generated with reportlab.

Re: Ask HN: What tools do you use for data munging and merging?

#30

Post by mipmap04 »

hn12345 wrote: post #21

Looks like you're looking for ETL solutions. It's funny reading some of the replies here, you can tell who's coming from a more BI background compared to a software engineering background. Anyways, I think Alteryx does this job really well. It's great for ETL that the average Joe can pick up and learn very quickly. Sure, the UX could be improved, but it's miles better than AWS' Data Pipeline and other tools. It is a…

I totally agree with your first sentence. As a former enterprise data warehouse architect, it's interesting seeing some of the responses. Surprised I'm not seeing more Informatica and SSIS. I'll also say SSIS is a good solution for nearly any data storage platform and is included in a SQL Server license so might be a good, cheap enterprise solution if you already have SQL Server.
Post reply on HN