JSON이 왜 한 장을 따로 받는가
WHY JSON DESERVES A DEDICATED CHAPTER
제2장에서 CSV, Parquet, 엑셀 파일, MySQL 데이터베이스 같은 여러 형식의 데이터를 DuckDB로 들여오는 법을 익혔다. 개발자들 사이에서 널리 쓰이는 또 하나의 중요한 파일 형식이 JSON(JavaScript Object Notation)이다.
JSON의 두드러진 특징 하나는 유연하고 동적인 구조다. 따라야 할 고정된 스키마가 없고, 동적인 키·값 쌍으로 자기 데이터를 표현할 수 있다. DuckDB에서 JSON 파일을 다루는 기법은 여럿이며, 그런 까닭에 JSON은 한 장을 따로 받을 만하다.
제2장에서 네 개의 문을 지날 때, 문턱이 가장 높았던 것은 엑셀이었다. 그런데 JSON은 문턱의 높낮이가 문제가 아니다. 문 안쪽의 구조가 파일마다 다르다는 것이 문제다. CSV는 어느 파일이든 행과 열이지만, JSON은 같은 확장자를 달고도 배열일 수 있고 줄바꿈으로 나뉜 낱개일 수 있고 세 겹으로 든 함일 수 있다. 그래서 이 장은 형식을 소개하는 장이 아니라 구조를 판별하고 그에 맞는 함수를 고르는 장이다.
이 장에서 살필 적재 수단은 셋이다.
- read_json_auto() 가장 직접적인 방법. 늘 이것을 먼저 시도한다.
- read_json() 자동 감지가 실패할 때 형식과 스키마를 손으로 지정한다.
- COPY-FROM 큰 데이터셋에 유리하다. 다만 표를 먼저 손으로 지어야 한다.
이 장은 다행히 결과의 상당 부분이 DuckDB CLI의 박스 출력과 파일 내용의 인용으로 지면에 인쇄되어 있다. 그런 값은 그대로 옮겼다. 도판 이미지에만 있는 표는 값을 옮기지 않고, 원서 본문이 그 도판에서 무엇을 지적했는지를 적었다.
표기 규칙은 앞 다섯 장과 같다. 문맥으로 복원되는 부분은 점선 밑줄, 복원하지 않은 부분은 …, 검토자가 다른 곳의 인쇄분과 대조해 채운 값은 표에서 점선을 두른 글자로 표시했다.
아울러 이 권만 강조색이 다르다. 구조색으로 금동(金銅)의 호박빛을 썼으므로, 앞 권들에서 강조로 쓰던 치자금을 먹청으로 바꿨다.
JSON의 여섯 가지 그릇
PRIMER ON JSON
JSON은 사람이 읽고 해석하기 쉽고 컴퓨터가 조작하고 생성하기에 매우 효율적인, 가벼운 데이터 표현 형식이다. 지원하는 데이터 유형은 여섯이다.
객체
object중괄호 한 쌍으로 감싼, 순서 없는 키·값 쌍의 모음이다. 키·값 쌍이 하나도 없는 객체도 가능하며 이를 빈 객체라 한다.
{ "key": "value" }
{}
문자열
string객체의 키는 반드시 문자열이어야 하며, 값은 문자열·불리언·숫자·배열·null 또는 또 다른 객체가 될 수 있다. 각 키·값 쌍은 쉼표로 나눈다. 마지막 쌍 뒤에는 쉼표가 필요하지 않다.
{
"firstName": "John",
"lastName": "Doe"
}
불리언
boolean참 또는 거짓, 곧 true 또는 false다.
"isMember": true,
"single": false,
숫자
number정수이거나 부동소수점 수다.
"weight": 79.5,
"height": 1.73,
"children": 3
배열
array객체의 순서 있는 나열이다. 대괄호 한 쌍으로 표시하고 모든 객체는 쉼표로 나눈다.
"phone": [
{"type":"work", "number":"1234567"},
{"type":"home", "number":"8765432"},
{"type":"mobile","number":"1234876"}
]
null
null키에 값이 없을 때 null을 넣는다.
"oldMembershipNo": null
객체의 각 키는 유일해야 한다. 예컨대 다음은 firstName 키가 둘이므로 유효한 JSON 문자열이 아니다.
{
"firstName": "John",
"firstName": "Doe"
}원서는 이 여섯 유형을 하나의 예시를 조금씩 키워 가며 설명한다. firstName 하나에서 시작해 이름·불리언·숫자·중첩 객체·배열·null까지 차례로 얹어, 마지막에는 한 사람에 관한 온전한 기록 하나가 완성된다. 아래가 그 최종 형태다. 이 한 덩어리 안에 여섯 유형이 모두 들어 있다.
{
"firstName": "John",
"lastName": "Doe",
"isMember": true,
"single": false,
"weight": 79.5,
"height": 1.73,
"children": 3,
"address": {
"line1": "123 Street",
"line2": "San Francisco",
"state": "CA",
"postal": "12345"
},
"phone": [
{ "type": "work", "number": "1234567" },
{ "type": "home", "number": "8765432" },
{ "type": "mobile", "number": "1234876" }
],
"oldMembershipNo": null
}주목할 것은 "single": false, 뒤의 쉼표다. 원서의 불리언 예시는 마지막 쌍 뒤에 쉼표를 두고 있어, 바로 앞에서 밝힌 “마지막 쌍 뒤에는 쉼표가 필요하지 않다”는 규칙과 어긋난다. 엄격한 파서라면 거부한다. 예시를 옮기되 이 점은 적어 둔다.
가장 먼저 시도하는 손 · read_json_auto()
USING THE READ_JSON_AUTO() FUNCTION
시연을 위해 json1.json이라는 파일의 내용을 살핀다. JSON 객체의 배열이며, 각 객체는 id, name, address, email, weight 다섯 필드를 담는다.
[
{
"id": 1,
"name": "Sarah Johnson",
"address": "4321 Oak Street Apartment 304…
"email":"sarah_johnson478@gmail.com",
"weight": 140.50
},
{
"id": 2,
"name": "David Martinez",
"address": "789 Maple Avenue Suite 102 Ne…
"email":"david_martinez431@gmail.com",
"weight": 155.0
},
{
"id": 3,
"name": "Emily Wilson",
"address": "567 Pine Road Unit 5B Chicago…
"email":"emily_wilson998@gmail.com",
"weight": 200.1
}
]import duckdb conn = duckdb.connect() conn.execute(''' SELECT * FROM read_json_auto('json1.json') ''').df() # 표로 남기려면 이렇게 고친다 conn.execute(''' CREATE TABLE People as FROM 'json1.json' ''')
read_json_auto() 함수는 JSON 파일의 여러 키·값 쌍을 자동으로 해석해 DuckDB로 적재한다. 결과가 다섯 열의 표이며, JSON 파일의 키·값 쌍과 맞아떨어진다.
제3장에서 다룬 DuckDB CLI로 같은 질의를 실행하면 다음 출력이 보인다. 여기서 중요한 것은 열마다 데이터 유형이 함께 표시된다는 점이다. 어떤 유형의 데이터가 적재되었는지 알아야 할 때 특히 도움이 된다.
D SELECT * FROM read_json_auto('json1.json'); ┌───────┬────────────────┬───┬───────────────────… │ id │ name │ … │ email │ int64 │ varchar │ │ varchar ├───────┼────────────────┼───┼───────────────────… │ 1 │ Sarah Johnson │ … │ sarah_johnson478@g… │ 2 │ David Martinez │ … │ david_martinez431@… │ 3 │ Emily Wilson │ … │ emily_wilson998@gm… ├───────┴────────────────┴───┴───────────────────… │ 3 rows 5 columns └────────────────────────────────────────────────…
records 매개변수 — 펼치지 않고 그대로 두기
각 객체를 한 행으로 적재하되 모든 키·값 쌍을 단일 열에 담고 싶다면 records 매개변수를 false로 설정한다.
conn.execute(''' SELECT * FROM read_json_auto('json1.json', records = false) ''').df()
기본적으로 records는 true다. JSON 확장이 JSON 객체를 기대하므로, 필드를 개별 열로 자동으로 풀어 놓는다. DuckDB CLI로 보면 출력이 이렇다.
D SELECT * FROM read_json_auto('json1.json', records = false); ┌────────────────────────────────────────────────… │ json │ struct(id bigint, "name" varchar, address varch… ├────────────────────────────────────────────────… │ {'id': 1, 'name': Sarah Johnson, 'address': 432… │ {'id': 2, 'name': David Martinez, 'address': 78… │ {'id': 3, 'name': Emily Wilson, 'address': 567… └────────────────────────────────────────────────…
이 출력에서 "name"만 이중 인용부호에 싸여 있는 것이 눈에 띈다. name이 SQL의 예약어에 가까운 이름이어서 식별자로 감싼 것이다. 자동 추론이 이름 하나까지 살펴 처리하고 있다는 흔적이다.
JSON 파일에서 특정 필드만 골라 적재할 수도 있다. 예컨대 name과 email 쌍만 적재하려면 SELECT 목록에 그 둘만 적는다.
conn.execute(''' SELECT name, email FROM read_json_auto('json1.json') ''').df()
대부분의 경우 read_json_auto()가 JSON 파일 적재의 일을 해낸다. 그러나 어떤 특수한 경우에는 손으로 적재해야 한다. 그럴 때 쓰는 것이 read_json()이다.
손으로 지정하는 손 · read_json()
USING THE READ_JSON() FUNCTION
read_json_auto()는 실은 자동 감지가 켜진 read_json()의 별칭이다. 대부분의 경우 read_json_auto()를 써야 한다. 드물게 그 함수가 파일의 형식을 자동으로 감지하지 못하는 경우가 있으며, 그때는 read_json()을 쓰고 데이터의 형식과 스키마를 지정한다.
두 함수의 관계가 이렇다는 사실이 이 절의 요점이다. 새 도구를 배우는 것이 아니라, 같은 도구의 자동 장치를 끄고 손으로 잡는 일이다.
| format 값 | 뜻 |
|---|---|
| array | JSON 객체의 배열, 곧 대괄호로 감싼 형태다. |
| newline_delimited · nd | 줄바꿈 문자로 나뉜 낱개의 JSON 객체들이다. |
| unstructured | 줄바꿈 구분 JSON을 포함해 어떤 종류의 JSON이든 담을 수 있다. 더 자유로운 형태나 불규칙한 JSON까지 아우른다. |
| auto | 형식을 자동으로 판별한다. |
객체의 배열
json1.json이 JSON 객체의 배열임을 앞에서 보았다. read_json()으로 그 내용을 읽으려면 형식을 auto로 두고, 어떤 열을 어떤 유형으로 적재할지 지정한다.
conn.execute(''' SELECT * FROM read_json('json1.json', format = 'auto', columns = { id:'INTEGER', name:'STRING', weight:'FLOAT' }) ''').df()
DuckDB CLI로 각 열의 유형을 확인할 수 있다. 이 출력은 원서 지면에 온전히 인쇄되어 있어, 이 장에서 값이 완전하게 확인되는 드문 대목이다.
D SELECT * FROM read_json('json1.json', format = 'auto', columns = { id:'INTEGER', name:'STRING', weight:'FLOAT' }); ┌───────┬────────────────┬────────┐ │ id │ name │ weight │ │ int32 │ varchar │ float │ ├───────┼────────────────┼────────┤ │ 1 │ Sarah Johnson │ 140.5 │ │ 2 │ David Martinez │ 155.0 │ │ 3 │ Emily Wilson │ 200.1 │ └───────┴────────────────┴────────┘
| id | name | weight |
|---|---|---|
| int32 | varchar | float |
| 1 | Sarah Johnson | 140.5 |
| 2 | David Martinez | 155.0 |
| 3 | Emily Wilson | 200.1 |
여기서 유형의 변화를 눈여겨보아야 한다. 자동 감지에서 id는 int64였으나, columns에 INTEGER를 지정하자 int32가 되었다. 손으로 잡는다는 것은 이런 종류의 통제권을 얻는 일이다. 그리고 140.50이라 적혀 있던 값이 140.5로 인쇄되는 것도 같은 이치다. JSON의 글자가 아니라 float이라는 유형이 그 값을 표현하고 있기 때문이다.
모든 키·값 쌍을 읽으려면 columns 매개변수를 그냥 생략한다.
conn.execute(''' SELECT * FROM read_json('json1.json', format = 'auto') ''').df()
한 줄에 한 함 · 줄바꿈 구분 JSON
NEWLINE-DELIMITED (ND) JSON
json1_a.json이라는 또 하나의 파일이 있다고 하자. 세 줄의 JSON 객체가 각각 줄바꿈 문자로 나뉘어 있다. 이를 줄바꿈 구분 JSON 파일이라 한다.
{"id": 1, "name": "Sarah Johnson", "address":
{"line1":"4321 Oak Street Apartment","line2"…
"state":"CA", "zip":90001}, "email":"sarah_j…
"weight": 140.50}
{"id": 2, "name": "David Martinez", "address":
{"line1":"789 Maple Avenue ","line2":"Suite…
"state":"NY","zip":10001}, "email":"david_…
"weight": 155.0}
{"id": 3, "name": "Emily Wilson", "address":
{"line1":"567 Pine Road Unit 5B Chicago",
"state":"IL", "zip":60601}, "email":"emily_w…
"weight": 200.1}이 인쇄분에서 두 가지가 확인된다. 첫째, 이 장의 세 사람의 주소가 실제로는 line1·line2·state·zip으로 나뉜 중첩 객체라는 것. 둘째, Emily Wilson의 객체에는 line2 키가 없다는 것이다. 이 결손은 우연이 아니라 뒤에서 되풀어 쓰이는 장치다. 중첩을 전개할 때, 여러 파일을 합칠 때, 그리고 COPY-FROM의 한계를 말할 때 모두 이 빠진 키가 등장한다.
# ① format을 newline_delimited로 지정한다 conn.execute(''' SELECT * FROM read_json('json1_a.json', format = 'newline_delimited', columns = { id:'INTEGER', name:'STRING', weight:'FLOAT' }) ''').df() # ② 전용 함수를 쓰면 format 지정이 필요 없다 conn.execute(''' SELECT * FROM read_ndjson_auto('json1_a.json', columns = { id:'INTEGER', name:'STRING', weight:'FLOAT' }) ''').df()
newline_delimited 외에 nd나 unstructured 형식도 쓸 수 있다. unstructured 형식은 줄바꿈 구분 JSON을 포함해 어떤 종류의 JSON이든, 더 자유로운 형태나 불규칙한 JSON까지 담을 수 있다.
겹을 여는 손 · 중첩 JSON
NESTED JSON
객체가 중첩된 또 하나의 예시 파일 json2.json을 본다. address 키의 값이 line1, line2, state, zip 네 개의 키로 다시 갈라진다. 예외는 세 번째 객체이며, address 키의 값에 line2 키가 없다.
[
{
"id": 1,
"name": "Sarah Johnson",
"address": {
"line1":"4321 Oak Street Apartment",
"line2":"304 Los Angeles",
"state":"CA",
"zip":90001
},
"email":"sarah_johnson478@gmail.com",
"weight": 140.50
},
{
"id": 2,
"name": "David Martinez",
"address": {
"line1":"789 Maple Avenue ",
"line2":"Suite 102 New York",
"state":"NY",
"zip":10001
},
"email":"david_martinez431@gmail.com",
"weight": 155.0
},
{
"id": 3,
"name": "Emily Wilson",
"address": {
"line1":"567 Pine Road Unit 5B Chicago",
// line2 키가 없다
"state":"IL",
"zip":60601
},
"email":"emily_wilson998@gmail.com",
"weight": 200.1
}
]conn.execute(''' SELECT * FROM read_json('json2.json') ''').df()
address 필드의 값을 개별 열로 표현하고 싶다면, 곧 line1·line2·state·zip 네 열을 갖고 싶다면 SQL 문에서 개별 키를 지정한다.
conn.execute(''' SELECT id, name, address['line1'] as line1, address['line2'] as line2, address['state'] as state, address['zip'] as zip, email, weight FROM read_json('json2.json') ''').df()
| id | name | line1 | line2 | state | zip |
|---|---|---|---|---|---|
| 1 | Sarah Johnson | 4321 Oak Street Apartment | 304 Los Angeles | CA | 90001 |
| 2 | David Martinez | 789 Maple Avenue | Suite 102 New York | NY | 10001 |
| 3 | Emily Wilson | 567 Pine Road Unit 5B Chicago | line2 키 없음 | IL | 60601 |
함 속의 함 · 이중 중첩
address 키 안에 또 하나의 중첩 객체가 있으면 어떻게 되는가. json2_a.json이 그 경우다. address 키의 값 안에 location 키가 또 있고, 그 안에 세 개의 키·값 쌍이 든다.
[
{
"id": 1, "name": "Sarah Johnson",
"address": {
"line1":"4321 Oak Street Apartment",
"line2":"304 Los Angeles",
"location" : {
"state":"CA",
"city":"Calexico",
"zip":90001
}
},
"email":"sarah_johnson478@gmail.com",
"weight": 140.50
},
{
"id": 2, "name": "David Martinez",
"address": {
"line1":"789 Maple Avenue ",
"line2":"Suite 102 New York",
"location" : {
"state":"NY",
"city":"Coney Island",
"zip":10001
}
},
"email":"david_martinez431@gmail.com",
"weight": 155.0
},
{
"id": 3, "name": "Emily Wilson",
"address": {
"line1":"567 Pine Road Unit 5B Chicago",
"location" : {
"state":"IL",
"city":"Brookfield",
"zip":60601
}
},
"email":"emily_wilson998@gmail.com",
"weight": 200.1
}
]conn.execute(''' SELECT address['line1'] as line1, address['line2'] as line2, address['location']['state'] as state, address['location']['city'] as city, address['location']['zip'] as zip, email, weight FROM read_json('json2_a.json') ''').df()
| line1 | state | city | zip |
|---|---|---|---|
| 4321 Oak Street Apartment | CA | Calexico | 90001 |
| 789 Maple Avenue | NY | Coney Island | 10001 |
| 567 Pine Road Unit 5B Chicago | IL | Brookfield | 60601 |
대괄호를 하나 더 이어 붙이는 것으로 한 겹을 더 들어간다. 문법이 단순한 만큼 정직하다. 다만 정직함에는 값이 따른다. 겹이 깊어질수록 SQL 문이 길어지고, 어느 겹에 무엇이 있는지를 사람이 미리 알고 있어야 한다. 자동 감지가 열을 만들어 주는 것은 첫 겹까지이며, 그 아래는 손으로 짚어야 한다. 발굴 기록을 남기지 않으면 다음 사람이 함을 열 수 없다는 사정과 같다.
배열을 눕히는 손 · unnest()
CUSTOM JSON FILE
되풀이되는 뚜렷한 양상이 없는 특정 구조를 담은 JSON 파일은 어떻게 다루는가. json3.json을 본다. people이라는 키 하나가 있고 그 값이 JSON 객체의 배열이다. 곧 파일의 최상위가 배열이 아니라 객체이며, 우리가 원하는 배열은 그 객체의 값으로 한 겹 들어가 있다.
{
"people": [
{
"id": 1,
"name": "Sarah Johnson",
"address": {
"line1":"4321 Oak Street Apartment",
"line2":"304 Los Angeles",
"state":"CA",
"zip":90001
},
"email":"sarah_johnson478@gmail.com",
"weight": 140.50
},
{
"id": 2,
"name": "David Martinez",
"address": { … line1 · line2 · state NY · zip 10001 … },
"email":"david_martinez431@gmail.com",
"weight": 155.0
},
{
"id": 3,
"name": "Emily Wilson",
"address": { … line1 · state IL · zip 60601, line2 없음 … },
"email":"emily_wilson998@gmail.com",
"weight": 200.1
}
]
}이 파일을 read_json()으로 적재해 본다. 흥미롭게도 people 키의 값이 단일 열로 적재된다.
# ① 그냥 읽는다 — people이 한 열이 된다 conn.execute(''' SELECT * FROM read_json('json3.json') ''').df() # ② unnest()로 배열의 원소를 행으로 바꾼다 conn.execute(''' SELECT unnest(people) p FROM read_json('json3.json') ''').df() # ③ 각 행의 내용을 p로 풀어 낸다 conn.execute(''' SELECT p.id, p.name, p.address['line1'] as line1, p.address['line2'] as line2, p.address['state'] as state, p.address['zip'] as zip, p.email, p.weight FROM ( SELECT unnest(people) p FROM read_json('json3.json') ) ''').df()
이 질의는 각 행의 내용을 대괄호로 감싸는 방식으로도 쓸 수 있다. 두 어법이 같은 일을 한다.
conn.execute(''' SELECT p['id'], p['name'], p['address']['line1'] as line1, p['address']['line2'] as line2, p['address']['state'] as state, p['address']['zip'] as zip, p['email'], p['weight'] FROM ( SELECT unnest(people) p FROM read_json('json3.json') ) ''').df()
이 절의 세 걸음이 이 장 전체의 구조를 압축한다. 배열은 눕히고, 객체는 연다. unnest()가 세로로 늘리고, 대괄호가 가로로 펼친다. 파일이 아무리 낯설게 생겼어도 이 두 동작의 조합으로 표가 된다. 이 장의 나머지는 어느 함수로 파일을 집어 드는가의 문제일 뿐이다.
여러 함을 한꺼번에
LOADING MULTIPLE JSON FILES
여기까지는 개별 JSON 파일을 적재했다. 그러나 여러 JSON 파일을 한꺼번에 적재해야 하는 경우가 잦다. json4.json과 json5.json을 본다.
# json4.json — 두 사람. 주소는 문자열, weight와 email이 있다 [ { "id": 1, "name": "Sarah Johnson", "address": "4321 Oak Street Apartment 304… "email":"sarah_johnson478@gmail.com", "weight": 140.50 }, { "id": 2, "name": "David Martinez", "address": "789 Maple Avenue Suite 102 Ne… "email":"david_martinez431@gmail.com", "weight": 155.0 } ] # json5.json — 한 사람. 주소는 객체, weight와 email이 없고 height가 있다 [ { "id": 3, "name": "Emily Wilson", "address": { "line1":"567 Pine Road Unit 5B Chicago", "state":"IL", "zip":60601 }, "height": 66 } ]
두 파일 모두 일반적인 구조는 같다. 객체의 배열이다. 핵심적인 차이는 각 객체의 구성이다. 두 번째 JSON 파일에는 weight와 email 키가 없고, address 키의 내용이 두 파일에서 서로 다르다. 이제 파일 이름의 목록을 read_json()에 넘겨 두 파일을 함께 적재한다.
conn.execute(''' SELECT * FROM read_json(['json4.json','json5.json']) ''').df()
DuckDB에서 여러 JSON 파일을 적재할 때 어떤 필드가 한 파일에서는 문자열 값이고 다른 파일에서는 숫자 값이면, DuckDB는 그 필드의 데이터 유형을 추론하려 시도한다. 유형 충돌이 있으면 일관성을 지키기 위해 숫자 값을 문자열로 변환할 수 있으며, 그 결과 열 전체가 문자열로 다루어진다. 충돌이 심한 경우에는 오류를 낼 수도 있다. 이를 피하려면 파일 사이의 데이터 유형 일관성을 보장하거나, 질의에서 그 필드를 원하는 유형으로 명시적으로 변환한다.
원서가 이 결과에서 관찰한 것은 셋이다.
- 합쳐진다 결과는 적재된 두 파일의 결합이다.
- 이름으로 맞춘다 결과의 열은 JSON 파일 안 키의 이름과 일치한다. JSON 파일 안에서 키의 순서는 중요하지 않다.
- 빈 자리를 채운다 각 행에서 빠진 필드에는 숫자 필드라면 NaN 값이, 문자열 필드라면 None 값이 들어간다.
| id | name | address | weight | height | |
|---|---|---|---|---|---|
| 1 | Sarah Johnson | 문자열 | sarah_johnson478@… | 140.5 | NaN |
| 2 | David Martinez | 문자열 | david_martinez431@… | 155.0 | NaN |
| 3 | Emily Wilson | 객체 | None | NaN | 66 |
일반적으로 여러 파일을 적재할 때는 모든 파일이 같은 구조를 갖도록 하는 것이 이상적이다.
두 JSON 파일이 같은 구조를 갖도록 보장하는 파이썬 스크립트를 언제든 작성할 수 있다.
이름으로 한 줌 집어 오기 · glob
적재할 파일 이름의 목록을 주는 대신, 흔히 와일드카드 구문이라 불리는 glob 구문을 쓸 수도 있다. 여러 JSON 파일을 읽는 데 다음 와일드카드를 쓴다.
| 와일드카드 | 하는 일 |
|---|---|
| * | 임의 개수의 임의 문자와 일치한다. 없는 경우도 포함한다. |
| ** | 임의 개수의 하위 디렉터리와 일치한다. 없는 경우도 포함한다. |
| ? | 임의의 한 문자와 일치한다. |
| [abc] | 대괄호 안에 주어진 문자 가운데 하나와 일치한다. |
| [a-z] | 대괄호 안에 주어진 범위에서 한 문자와 일치한다. |
# json으로 시작하고 .json으로 끝나는 현재 디렉터리의 모든 파일 conn.execute(''' SELECT * FROM read_json('json*.json') ''').df() # json 다음에 한 글자가 오고 .json으로 끝나는 파일 conn.execute(''' SELECT * FROM read_json('json?.json') ''').df()
? 하나가 _a가 붙은 두 파일을 걸러 낸다. 파일 이름을 짓는 방식이 곧 질의의 도구가 된다는 점에서, 이 절은 데이터 정리의 기초를 말하고 있다. 이름을 규칙 있게 붙여 두면 나중에 한 줌씩 집어 올 수 있다.
큰 짐을 옮기는 손 · COPY-FROM
USING THE COPY-FROM STATEMENT
JSON 파일을 DuckDB로 적재하는 세 번째 방법은 COPY-FROM 문이다. 이는 특히 큰 데이터셋을 상대할 때 read_json()보다 상당한 이점을 제공한다.
| 견주는 자리 | COPY-FROM | read_json() |
|---|---|---|
| 적재 방식 | 데이터를 일괄(batch)로 적재해 큰 파일을 더 효율적으로 다룬다. 메모리 소비가 줄고 속도가 오른다. | 파일 전체를 메모리에 적재한다. 큰 파일에서 성능 병목을 낼 수 있다. |
| 병렬성 | DuckDB의 병렬 처리 능력을 온전히 활용해, 기가바이트 단위 데이터를 적재할 때 성능을 더 끌어올린다. | — |
| 작업 흐름 | 최소한의 전처리로 데이터를 표에 직접 적재하고 스키마를 자동 추론해 데이터 일관성을 보장하므로, 데이터베이스 작업 흐름에 더 매끄럽게 들어맞는다. | 스키마 일관성을 강제하거나 데이터를 데이터베이스에 넣기 위해 추가 단계가 필요한 경우가 많다. |
| 적합한 자리 | 고성능 대규모 데이터 반입 | 더 작고 대화적인 작업 |
COPY-FROM 문으로 JSON 파일을 적재하려면 먼저 JSON 구조에 맞는 올바른 스키마로 표를 만들고, 그다음 COPY-FROM으로 파일을 적재한다.
conn = duckdb.connect() conn.execute(''' CREATE TABLE people (id INT, name STRING, address STRING, email STRING, weight FLOAT); COPY people FROM 'json1.json' (FORMAT JSON, AUTO_DETECT true); SELECT * FROM people; ''').df()
JSON 파일을 제대로 적재하려면 (FORMAT JSON, AUTO_DETECT true) 문을 써야 한다. 그리고 JSON 파일이 복잡한 구조를 가진 경우, 예컨대 json5.json 같은 경우에는 이 방법을 권하지 않는다.
대가 관계가 뚜렷하다. COPY-FROM은 빠르고 크게 다루지만, 스키마를 미리 알고 손으로 적어야 한다. 중첩된 함이나 파일마다 다른 구성 앞에서는 그 미리 아는 일이 불가능해진다. 제2장에서 COPY가 큰 CSV에 최고의 성능을 내면서도 엑셀에는 아예 쓸 수 없었던 그 대비가 여기서 되풀어진다. 빠른 길에는 늘 정해진 모양의 짐만 오른다.
다시 함에 담아 내보내다
EXPORTING TABLES TO JSON
DuckDB에 있는 표를 JSON 파일로 남기고 싶을 때가 있다. 다른 플랫폼과 데이터를 교환하려 할 때, 또는 단순히 데이터를 백업하는 방법으로 유용하다. 앞 절에서 다룬 COPY 문으로 쉽게 내보낸다.
conn = duckdb.connect() conn.execute(''' CREATE OR REPLACE TABLE people as SELECT name, weight FROM read_ndjson_auto('json1_a.json') ''') display(conn.execute('SELECT * FROM people').df()) conn.execute(''' COPY people TO 'people.json' (FORMAT JSON); ''')
people.json의 내용은 이렇다.
{"id":1,"name":"Sarah Johnson","weight":140.5}
{"id":2,"name":"David Martinez","weight":155.0}
{"id":3,"name":"Emily Wilson","weight":200.1}앞의 CREATE OR REPLACE TABLE 문은 name과 weight 두 열만 골라 표를 만든다. 그런데 여기 인쇄된 people.json에는 id가 함께 들어 있다. 뒤에 나오는 people_array.json에는 id가 없다. 두 출력이 서로 어긋나므로, 원서 지면의 어느 한쪽이 다른 시점의 실행 결과로 보인다. 양쪽을 그대로 옮기되 이 어긋남을 적어 둔다. 질의문을 따른다면 두 열만 나오는 쪽이 맞다.
내용이 엄격한 표준 JSON이 아니라는 점을 눈여겨본다. 실은 줄바꿈 구분 JSON 파일이며, 각 줄이 별개의 JSON 객체를 담는다. 출력을 엄격한 JSON으로 만들려면 ARRAY TRUE를 지정한다.
conn.execute(''' COPY people TO 'people_array.json' (ARRAY TRUE); ''') # people_array.json의 내용 [ {"name":"Sarah Johnson","weight":140.5}, {"name":"David Martinez","weight":155.0}, {"name":"Emily Wilson","weight":200.1} ]
기본값이 줄바꿈 구분이라는 사실은 우연이 아니다. 대괄호로 전체를 감싸려면 마지막 줄이 언제인지 알아야 하고, 그러려면 스트림을 끝까지 붙들고 있어야 한다. 반면 한 줄에 한 객체를 쓰는 방식은 쓰는 즉시 끝난 줄이 되므로 얼마든 이어 붙일 수 있다. 이 장의 첫머리에서 만난 그 형식이 마지막에 기본값으로 돌아오는 셈이다.