Sie wurden mit einer der folgenden Aufgaben beauftragt:
– Laden Sie eine neue CSV-Datei zur Analyse in BigQuery.
– Fügen Sie Ihrer Datenpipeline eine neue Datei hinzu.
– Führen Sie einige geringfügige Datenqualitätsprüfungen für die Dateiablage eines Anbieters durch

Sie greifen nach dem nächstgelegenen und vertrautesten Werkzeug, das Sie kennen. Normalerweise geht es darum, alles in Excel zu laden, oder? Das bringt Sie in Schwung. Aber die Datei ist 600 MB groß und über 1,2 Millionen Zeilen lang. Excel teilt Ihnen mit, dass die Datei nur teilweise geladen werden kann.

Ok, was kommt als nächstes?

Sie befinden sich möglicherweise auf GCP/BigQuery und führen das Click-and-Drop-Prinzip aus, aber BQ teilt Ihnen mit, dass die Position 1023845 nicht gut ist. Wie findet man überhaupt diese Byte-Position in einer Datei? <seufz>

Sie durchsuchen Stackoverflow und werden aufgefordert, Pandas zu verwenden. Es bringt Sie zwar bis zum Ziel, aber Ihre Geschwindigkeit wird beeinträchtigt, weil Sie jeden Befehl vergessen haben. Vielleicht verwenden Sie ChatGPT, benötigen aber sofort Antworten, und zwar schnell.

Wäre es nicht cool, einfach * aus der Ausgabe.csv auszuwählen?

Nun, das können Sie durchaus.

Schritt 1

brew install duckdb

> duckdb
D> select * from ‘output.csv*’

Sie können sogar „data/*.csv“ oder sogar andere Erweiterungen wie „data/*.parquet“ ausführen.

Fehlerbehebung

Das Laden der CSV-Datei läuft also nicht so gut. Sie haben mit duckdb angefangen, heißt es

Error: Invalid Input Error: Could not convert string ‘Column_1’ to INT32 
in column “Column_1”, at line 100002.

Als Erstes sollten wir über Ihrer CSV-Datei eine Ansicht mit einigen Zeilennummern erstellen, damit Sie mit der Überprüfung der Datei und einigen leichten Qualitätsprüfungen beginnen können.

create sequence seq_id start 1;

D> create or replace view test_1 as SELECT nextval(‘seq_id’) as seq_id, * 
from read_csv(‘output.csv’, ALL_VARCHAR=1, AUTO_DETECT=TRUE);

couple of things here, of reference is https://duckdb.org/docs/data/csv

• Sie haben schlechte Daten. Duckdb tastet alle 100 Zeilen ab und errät den Spaltentyp. Einige Daten in Ihrer Spalte sind nicht das, was sie sein sollten. „ALL_VARCHAR“ ermöglicht es Ihnen, alles zu laden, unabhängig vom Typ zur Analyse
  – „AUTO_DETECT“ bedeutet, dass Sie anhand der ersten Zeile erraten, wie viele Spalten vorhanden sein sollten. Andernfalls müssten wir sie manuell eingeben: „columns={'Column_1,', 'Column_2…}'“.

Da unsere Sequenz an dieser Stelle immer zählt, ist es ein guter Zeitpunkt, sie zu sperren und die Ansicht mit unseren Zeilennummern zu materialisieren.

create table test_1_mv as (select * from test_1);

 — it seems that line numbers in errors are zero-indexed
 — however you cannot start a sequence from 0
 — so we need to subtract `1` from the id
select * from test_1 where seq_id = 100001
┌────────┬───────────────┬──────────────┬──┐
│ seq_id │ Column_1      │ Column_2     │  │
│ int64  │ varchar       │ varchar      │  │
├────────┼───────────────┼──────────────┼──┤
│ 100001 │ Column_1_text │ Column_2_text│  │
├────────┴───────────────┴──────────────┴──┤
│ 1 rows                                   │
└──────────────────────────────────────────┘

Suchen wir alle fehlerhaften Zeilen. Mein erster Gedanke ist, nach allen Zeilen zu suchen, die „Column_1_text“ enthalten.

select * from test_1 where column_1 = ‘Column_1_text’;

select * from test_1
where regexp_matches(Column_1, ‘[a-zA-Z]’)

delete from test_1_mv where seq_id in (select seq_id from test_1_mv 
where regexp_matches(Column_1, ‘[a-zA-Z]’));

Ausschreiben der neuen Datei – Exportieren

Um die Daten aus einer Tabelle in eine CSV-Datei zu exportieren, verwenden Sie die Anweisung „COPY“.

COPY test_1_mv TO ‘output_cleaned.csv’ (HEADER, DELIMITER ‘,’);