Hacker News Reader

Comment by axus | Hacker News Reader

The article mentioned CSV files, it seems more like a reflection of what a huge bureaucracy the US healthcare system is. I liked their suggestion that the government should have created the database as part of the law, done the processing on the raw data, and made it more accessible.

View on HN

meh, I'd prefer the raw data. We can always create DBs out of the raw data, we can always link data. Handling this after the fact would be impossible.

Linking a few trillion records doesn't seem that difficult. It should be doable with a good data warehouse and a reasonable entity linking model. I suspect that we'll find more than a few instances of fraudulent behavior once the data is linked.

My father was nearly pushed into ~2 Million dollars worth of brain surgery that was unnecessary. Not only was the procedure unnecessary, the price for it was >5X what a top-3 hospital would have charged. I only became privy to this once I pushed him to come to Mass General Hospital (MGH) for a second opinion. The surgeon we saw at MGH also believed the suggested procedure to be dangerous.

I wonder if it's possible to cross-reference mortality/complication rates with prices...

import a CSV into Postgres

    with open(filepath) as fd:
        first_line = fd.readline()
        cols = []
        for col in first_line.strip().split(','):
            col2 = f'''"{col.strip('"')}" text'''
            cols.append(col2)
        cols2 = ','.join(cols)
        print(f"create table {table_name} ({cols2});")
    print(f"\copy {table_name} from '{filepath}' csv header;")

this variant will ingest whatever trash is in your CSV fields as-is (cast & cleanup later)

run the output in a psql instance connected to your db

(important note: \copy is a psql client command and it is critical to use \copy instead of COPY in many cases where the server process may not have the permission to read your CSV file. with \copy you can read any file the user that launched psql client has permission to read. to make things more confusing it is indeed possible to stream stdin through psql but you use the regular COPY for that instead of \copy)

Loading up 100TB of csv data into a Postgres db is not a realistic proposition.

Do NOT read a CSV file by splitting on commas. Python has a perfect CSV library built right in: https://docs.python.org/3/library/csv.html.

If you split on commas, your code will fail for quoted fields with commas in them.

This is obviously a quick and dirty hack. It only splits the header by commas. Postgres has its own read in logic you can adjust by passing arguments to \copy or copy. If you have anything more complex you need to handle it by parsing the header more intelligently than splitting on commas and adding appropriate arguments to copy or \copy

In their defense, if it was anything but CSV files, they would be accused of making it too complicated/locking into proprietary formats and so on. I can't say CSV would be my first choice, but I don't really want to think what the alternative would be.

Millions of .xlsx files

NDJSON? Sqlite?

csv is many times over again a simpler format than sqlite and easier to understand by anyone across the world.

Never heard of ndjson, can’t see one publishing this data in a format that isn’t nearly as common as something like csv (or regular json which some of the data is published in).

CSV only seems simple. Lots of parsing edge cases. Sqlite isn't readable by hand but it's basically bulletproof. NDJSON is literally just newline seperated JSON, it's just easier to process as a stream without a special parser.

Sure, could be simpler, but there's a spec and multiple implementations: https://www.rfc-editor.org/rfc/rfc4180.html And the spec is ~5 pages.

Not sure why NDJSON is considered simpler, as json objects can be arbitrarily nested. Breaking into records is easier, but parsing is harder.

(ND)JSON is simpler because people actually follow the spec, unlike with CSV.

People violate the JSON spec all the time.

And following the CSV spec is much easier than following the JSON spec. And there's only like three edge cases.

> And there's only like three edge cases.

Unless you involve MS Excel or, worse, MS Excel on macOS. OTOH, pitfalls are: UTF-8 BOM, comma vs semicolon, single vs double quote, multiline cell content and escaping, escaping in general...

Then you’d have to get your hands on a parser. Who wants to write up the business case and spend months going through change control (they’d probably just say no).

JSON and NDJSON can also be much larger than CSV files if the wrong structure is used.

All this banter arguing over CSV, JSON, sqlite seems unnecessary when you can just push format X through a pipe and get whichever format Y you want back out: https://github.com/liquidaty/zsv/blob/main/docs/csv_json_sql...

(disclaimer: I'm one of the zsv authors)

Reply on news.ycombinator.com