repro.js

import { Database } from 'duckdb-async';
import fs from 'fs';
import readline from 'readline';

// Check if the file exists
const path = '/tmp/localpy.duckdb';
const fileExists = fs.existsSync(path);

const db = await Database.create(path);

// Connect to the database
async function connectToDatabase() {

  // Install httpfs
  await db.run('install httpfs');

  // Create tables and insert data if the file doesn't exist
  if (!fileExists) {
    await db.run(`
      create table taxi as
      select *, md5(total_amount), md5(tpep_pickup_datetime), md5(tpep_dropoff_datetime)
      from parquet_scan('https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2022-01.parquet')
    `);
    await db.run(`
      insert into taxi
      select *, md5(total_amount), md5(tpep_pickup_datetime), md5(tpep_dropoff_datetime)
      from parquet_scan('https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2022-01.parquet')
    `);
    await db.run(`
      create table taxi_relationships as
      select vendorID, vendorID + 1 as follower
      from taxi
    `);
  }

  // Query the database
  for (let i = 0; i < 100; i++) {
    await new Promise(resolve => {
      const line = readline.createInterface({
        input: process.stdin,
        output: process.stdout
      });
      line.question('Press Enter to continue...', () => {
        line.close();
        resolve();
      });
    });

    const result = await db.all(`
      with "inner" as (
        select r.follower as "id"
        from "taxi_relationships" as "r"
        where "r"."VendorID" in ('1', '2')
        group by "id"
      )
      select *
      from "inner"
      inner join "taxi" as "u" on "inner"."id" = "u"."VendorId"
      order by u.total_amount desc limit 30
    `);
    console.log(result);
    console.log('Query 1 executed');
  }

  // Close the database connection
  await db.close();
}

connectToDatabase();