Parquet cleaning example in DuckDb SQL

goodreads-cleanup.sql

-- This should all work with the DuckDb shell CLI, available from https://duckdb.org/docs/installation/index
-- (Select the CLI tab)

-- first create a view on the Parquet file:
CREATE VIEW books AS SELECT * from './goodreads_books.parquet';

-- take a look at the schema:
PRAGMA table_info(books);

-- Fix up one of the columns (book_id), converting it from VARCHAR to INTEGER (int32):
CREATE TABLE cleaned_books AS SELECT * REPLACE (CAST(book_id AS INTEGER) AS book_id) FROM books;

-- take a look at cleaned up schema:
PRAGMA table_info(cleaned_books);

-- write back out as Parquet:
COPY (SELECT * FROM cleaned_books) TO './goodreads_books_cleaned.parquet' (FORMAT 'parquet');