노트북 한 대로 17억 행을 상대하는 마지막 장

# 인터넷 아카이브의 스택 익스체인지 덤프. 크리에이티브 커먼즈 라이선스다.
$ curl -OL "https://archive.org/download/stackexchange/stackoverflow.com-\
{Comments,Posts,Votes,Users,Badges,PostLinks,Tags}.7z"

# 압축된 XML 일곱 개, 합계 27GB
 19G  stackoverflow.com-Posts.7z          343M  stackoverflow.com-Badges.7z
5.2G  stackoverflow.com-Comments.7z       117M  stackoverflow.com-PostLinks.7z
1.3G  stackoverflow.com-Votes.7z          903K  stackoverflow.com-Tags.7z
684M  stackoverflow.com-Users.7z

# 파일은 SQL Server 내보내기 형식이다. Row 원소 하나가 모든 컬럼을 속성으로 갖는다.
<row Id="728812" Reputation="41063" CreationDate="2011-04-28T07:51:27.387"
     DisplayName="Michael Hunger" … Views="7046" UpVotes="4712" DownVotes="24" />

# DuckDB는 아직 XML 파싱을 지원하지 않으므로 외부 도구를 쓴다.
# xidel(XML → JSON) → jq(JSON → CSV) → gzip
$ 7z e -so stackoverflow.com-Comments.7z | \
  xidel -se '//row/[(@Id|@PostId|@Score|@Text|@CreationDate|@UserId)]' - | \
  (echo "Id,PostId,Score,Text,CreationDate,UserId" &&
   jq -r '. | @csv') |
  gzip -9 > Comments.csv.gz

# 끝나면 압축 CSV 일곱 개, 합계 11GB
5.0G  Comments.csv.gz     452M  Badges.csv.gz
3.2G  Posts.csv.gz        137M  PostLinks.csv.gz
1.6G  Votes.csv.gz        1.1M  Tags.csv.gz
613M  Users.csv.gz

D SELECT count(*) FROM read_csv('Tags.csv.gz');   -- 6만 5천 개에 조금 못 미친다

D DESCRIBE(FROM read_csv('Tags.csv.gz'));

D SELECT TagName, Count
  FROM read_csv('Tags.csv.gz', column_names=['Id', 'TagName', 'Count'])
  ORDER BY Count DESC LIMIT 5;

D SELECT cast(pow(10,floor(log(Count)/log(10))) AS INT) AS bucket,
         count(*)
  FROM read_csv('Tags.csv.gz', column_names=['Id', 'TagName', 'Count'])
  WHERE Count > 0            -- 사용 횟수가 0인 태그를 걸러낸다
  GROUP BY bucket
  ORDER BY bucket ASC;

D CREATE OR REPLACE TABLE users AS
  SELECT * FROM read_csv('Users.csv.gz', auto_detect=true,
      column_names=['Id', 'Reputation', 'CreationDate', 'DisplayName',
                    'LastAccessDate', 'AboutMe', 'Views', 'UpVotes', 'DownVotes']);
D SELECT count(*) FROM users;      -- 대략 2,000만 사용자

D CREATE OR REPLACE TABLE posts AS
  FROM read_csv('Posts.csv.gz', auto_detect=true,
      column_names=[
          'Id', 'PostTypeId', 'AcceptedAnswerId', 'ParentId', 'CreationDate',
          'Score', 'ViewCount', 'Body', 'OwnerUserId', 'LastEditorUserId',
          'LastEditorDisplayName', 'LastEditDate', 'LastActivityDate', 'Title',
          'Tags', 'AnswerCount', 'CommentCount', 'FavoriteCount',
          'CommunityOwnedDate', 'ContentLicense'
      ]);
D SELECT count(*) FROM posts;      -- 5,800만 게시물

D select column_name, column_type from (show table posts);
-- Id · PostTypeId · AcceptedAnswerId · CreationDate · Score · ViewCount · Body
-- OwnerUserId · LastEditorUserId · LastEditorDisplayName · LastEditDate
-- LastActivityDate · Title · Tags · AnswerCount · CommentCount · FavoriteCount
-- CommunityOwnedDate · ContentLicense
-- 19 rows   2 columns

D .timer on
D SELECT DisplayName, Reputation, LastAccessDate
  FROM users ORDER BY Reputation DESC LIMIT 5;
Run Time (s): real 0.126 user 2.969485 sys 1.696962

D SELECT DisplayName, reputation,
         round(reputation/day(today()-CreationDate)) as rate,   -- 일당 평판
         day(today()-CreationDate) as days,
         CreationDate
  FROM users
  WHERE reputation > 1_000_000          -- 숫자 리터럴에 밑줄을 쓸 수 있다
  ORDER BY rate DESC;
Run Time (s): real 0.006 user 0.007980 sys 0.001260

D SELECT
    year(CreationDate) AS year,
    round(count(*)/1000000,2) as postM,                          -- 게시물(백만)
    round(count_if(postTypeId = 1)/1000000,2) as questionM,      -- 질문(백만)
    round(count_if(postTypeId = 2)/1000000,2) as answerM,        -- 답변(백만)
    round(count_if(postTypeId = 1)/count_if(postTypeId = 2),2) as ratio,
    round(avg(ViewCount)) as avgViewCount,
    max(AnswerCount) as maxAnswerCount
  FROM posts GROUP BY year ORDER BY year DESC LIMIT 10;

Run Time (s): real 5.977 …  (첫 실행)
Run Time (s): real 0.039 …  (두 번째 실행)

D SELECT count(*) as freq,
         dayname(CreationDate) AS day,
         bar(freq, 0, 150000, 20) AS plot
  FROM posts WHERE posttypeid = 1
    AND tags LIKE '%<sql>%'
  GROUP BY all ORDER BY freq DESC;
Run Time (s): real 0.303 …    (질문 2,350만 건 처리)

-- 목록 10.14 · 열거형 타입 만들기의 기본형
D CREATE TYPE weekday AS enum (
    'monday', 'tuesday', 'wednesday', 'thursday', 'friday', 'saturday', 'sunday');

-- 값은 문자열로도 접근하고(  'saturday'  ) 타입을 붙여서도 접근한다( saturday::weekday )
-- 타입 자체는 NULL 값으로 가리킨다( null::weekday )

-- 목록 10.15 · tags 테이블의 값에서 태그 열거형을 만든다
D CREATE TYPE tag AS enum (SELECT DISTINCT tagname FROM tags);

-- 목록 10.16 · 열거형 값 몇 개 들여다보기
D select enum_range(null::tag)[0:5];
[textblock, idioms, haskell, flush, etl]

-- 목록 10.17 · 중간 단계로 문자열 배열 컬럼을 먼저 더한다
D ALTER TABLE posts ADD tagNames VARCHAR[];

-- 목록 10.18 · '<sql><duckdb>'에서 앞뒤 한 글자를 떼고 '><'로 쪼갠다
D UPDATE posts SET tagNames = split(tags[2:-2],'><')
  WHERE posttypeid = 1;
-- Run Time (s): real 51.120 …   (질문 2,350만 건)

-- 목록 10.19 · 열거형 배열 컬럼을 더한다
D ALTER TABLE posts ADD tagEnums tag[];

-- 목록 10.20 · 문자열 배열을 열거형 배열에 그냥 대입한다
D UPDATE posts SET tagEnums = tagNames WHERE posttypeid = 1;

D EXPLAIN ANALYZE
  SELECT year(CreationDate) AS year, count(*),
         round(avg(ViewCount)), max(AnswerCount)
  FROM posts GROUP BY year ORDER BY year DESC LIMIT 10;

-- 목록 10.26 · users
D COPY (FROM users) TO 'users.parquet'
    (FORMAT PARQUET, CODEC 'SNAPPY', ROW_GROUP_SIZE 100000);
-- Run Time (s): real 10.582 …

-- 목록 10.27 · posts
D COPY (FROM posts) TO 'posts.parquet'
    (FORMAT PARQUET, CODEC 'SNAPPY', ROW_GROUP_SIZE 100000);
-- Run Time (s): real 57.314 …

-- 목록 10.28 · 정렬해서 스레드마다 한 파일씩 쓴다
D COPY (
      SELECT * FROM users ORDER BY LastAccessDate DESC
  ) TO 'users.parquet'
    (FORMAT PARQUET, CODEC 'SNAPPY', PER_THREAD_OUTPUT TRUE);

-- 목록 10.29 · 10.30 · 같은 답을 두 형식에서 얻는 시간
D SELECT count(*) FROM read_parquet('users.parquet');
19942787   (0.008s)
D SELECT count(*) FROM read_csv_auto('Users.csv.gz');
19942787   (약 7s)

-- 목록 10.31 · 데이터베이스 전체를 내보낸다
D EXPORT DATABASE 'target_directory' (FORMAT PARQUET);
-- Parquet 파일들 + schema.sql + load.sql 이 만들어진다
D IMPORT DATABASE 'source_directory';

-- 목록 10.34 · 자기 버킷을 쓸 때만 필요하다. 이 버킷은 공개 읽기가 가능하다.
D CREATE [PERSISTENT] SECRET (
      TYPE S3,
      KEY_ID 'AKIA...',
      SECRET ''Sr8VSfK...',        -- 겹따옴표가 잘못 들어가 있다
      REGION 'us-east-1');

-- 목록 10.35 · S3 접근에 쓰이는 확장. 필요하면 자동 적재된다.
D INSTALL httpfs; LOAD httpfs;

-- 파일 하나. 메타데이터로 답하므로 600ms.
D SELECT count(*) FROM
    's3://us-prd-md-duckdb-in-action/nyc-taxis/yellow_tripdata_2022-06.parquet';
3,558,124 rows in 600 ms

-- 글로브 와일드카드. 파일 175개, 17억 행을 11초에 센다.
D SELECT count(*) FROM
    's3://us-prd-md-duckdb-in-action/nyc-taxis/yellow_tripdata_*.parquet';
1,721,158,822 rows in 11s

-- 특정 파일 몇 개만 고르려면 read_parquet를 직접 부른다.
D SELECT count(*) FROM read_parquet([
      '…/yellow_tripdata_2021-06.parquet',
      '…/yellow_tripdata_2022-06.parquet']);
6,392,388

-- 목록 10.36 · 2020년 이후 파일만 덮는 뷰. 즉시 돌아온다. 데이터를 읽지 않는다.
D CREATE OR REPLACE VIEW allRidesView AS
  FROM 's3://us-prd-md-duckdb-in-action/nyc-taxis/yellow_tripdata_202*.parquet';
-- 약 1억 1,800만 행

D FROM parquet_schema('…/yellow_tripdata_2022-06.parquet') SELECT name, type;

-- 목록 10.38 · 음수 거리를 걸러내도록 뷰를 다시 정의한다
D CREATE OR REPLACE VIEW allRidesView AS
  FROM '…/yellow_tripdata_202*.parquet' WHERE trip_distance > 0;

-- 목록 10.39 · 컬럼 하나만 요약하면 3초대로 줄어든다
D SUMMARIZE (SELECT trip_distance FROM allRidesView);
count = 115976028 · avg = 5.4306541 · std = 536.03 · q50 = 1.827  (real 3.033)

-- 목록 10.40 · 집계 함수만 쓰면 1초 아래로 떨어진다
D SELECT min(trip_distance), max(trip_distance), avg(trip_distance),
         stddev(trip_distance), count(trip_distance) AS nonNull,
         count(*) as total, 1-(nonNull/total) AS nullPercentage
  FROM allRidesView;
(real 0.747)

-- 목록 10.41 · 컬럼 여럿에 한꺼번에 — 정규식과 람다를 둘 다 쓴다
D SELECT count(*),
         min(columns(*)), max(columns(*)),
         round(avg(columns('_(distance|amount|tax|surcharge|fee)')),2),
         round(stddev(columns(c ->
             c SIMILAR TO '.+(distance|amount|tax|surcharge|fee)')),2)
  FROM allRidesView;
(real 6.813)

D SELECT year(tpep_pickup_datetime) AS year,
         round(avg(trip_distance)) AS dist,
         round(avg(fare_amount),2) AS fare,
         round(AVG(fare_amount/trip_distance),2) AS rate,
         count(*) AS trips
  FROM allRidesView
  GROUP BY year
  HAVING year BETWEEN 2020 AND 2024   -- 그룹화 키이므로 HAVING에서 걸러야 한다
  ORDER BY year;
Run Time (s): real 1.831 …

-- 목록 10.43 · 승객 수별 운행
D SELECT passenger_count, count(*)
  FROM allRidesView
  WHERE passenger_count < 10
  GROUP BY passenger_count
  ORDER BY count(*) DESC;
Run Time (s): real 0.897 …