ํ…Œ์ด๋ธ” ํ˜•์‹ Java UDF(UDTF)ยถ

์ด ๋ฌธ์„œ๋Š” Java์—์„œ UDTF(์‚ฌ์šฉ์ž ์ •์˜ ํ…Œ์ด๋ธ” ํ•จ์ˆ˜ )๋ฅผ ์ž‘์„ฑํ•˜๋Š” ๋ฐฉ๋ฒ•์„ ์„ค๋ช…ํ•ฉ๋‹ˆ๋‹ค.

์ด ํ•ญ๋ชฉ์˜ ๋‚ด์šฉ:

์†Œ๊ฐœยถ

Java UDTF ์ฒ˜๋ฆฌ๊ธฐ ํด๋ž˜์Šค๋Š” UDTF ํ˜ธ์ถœ์—์„œ ์ˆ˜์‹ ํ•œ ํ–‰์„ ์ฒ˜๋ฆฌํ•˜๊ณ  ํ…Œ์ด๋ธ” ํ˜•์‹ ๊ฒฐ๊ณผ๋ฅผ ๋ฐ˜ํ™˜ํ•ฉ๋‹ˆ๋‹ค. ์ˆ˜์‹ ๋œ ํ–‰์€ Snowflake์— ์˜ํ•ด ์•”์‹œ์ ์œผ๋กœ ๋ถ„ํ• ๋˜๊ฑฐ๋‚˜ ํ•จ์ˆ˜ ํ˜ธ์ถœ ๊ตฌ๋ฌธ์—์„œ ๋ช…์‹œ์ ์œผ๋กœ ๋ถ„ํ• ๋ฉ๋‹ˆ๋‹ค. ํด๋ž˜์Šค์—์„œ ๊ตฌํ˜„ํ•˜๋Š” ๋ฉ”์„œ๋“œ๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ๊ฐœ๋ณ„ ํ–‰๋ฟ ์•„๋‹ˆ๋ผ ํ–‰์ด ๊ทธ๋ฃนํ™”๋œ ํŒŒํ‹ฐ์…˜๋„ ์ฒ˜๋ฆฌํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

์ฒ˜๋ฆฌ๊ธฐ ํด๋ž˜์Šค๋Š” ๋‹ค์Œ์„ ์‚ฌ์šฉํ•˜์—ฌ ํŒŒํ‹ฐ์…˜๊ณผ ํ–‰์„ ์ฒ˜๋ฆฌํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

  • ์ด๋‹ˆ์…œ๋ผ์ด์ €๋กœ ์‚ฌ์šฉ๋˜๋Š” ์ธ์ž ์—†๋Š” ์ƒ์„ฑ์ž. ์ด ์ƒ์„ฑ์ž๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ํŒŒํ‹ฐ์…˜ ๋ฒ”์œ„ ์ง€์ • ์ƒํƒœ๋ฅผ ์„ค์ •ํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

  • ๊ฐ ํ–‰์„ ์ฒ˜๋ฆฌํ•˜๋Š” process ๋ฉ”์„œ๋“œ.

  • ๋ฒ”์œ„ ์ง€์ •๋œ ๊ฐ’์„ ํŒŒํ‹ฐ์…˜์œผ๋กœ ๋ฐ˜ํ™˜ํ•˜๋Š” ๊ฒƒ์„ ํฌํ•จํ•˜์—ฌ, ํŒŒํ‹ฐ์…˜ ์ฒ˜๋ฆฌ๋ฅผ ์™„๋ฃŒํ•˜๊ธฐ ์œ„ํ•œ ํŒŒ์ด๋„๋ผ์ด์ €๋กœ ์‚ฌ์šฉ๋˜๋Š” ์ธ์ž ์—†๋Š” endPartition ๋ฉ”์„œ๋“œ.

์ž์„ธํ•œ ๋‚ด์šฉ์€ ์ด ํ•ญ๋ชฉ์˜ UDTFs์šฉ Java ํด๋ž˜์Šค ์„น์…˜์„ ์ฐธ์กฐํ•˜์‹ญ์‹œ์˜ค.

๊ฐ Java UDTF์—๋Š” ํ•ธ๋“ค๋Ÿฌ ํด๋ž˜์Šค์— ์˜ํ•ด ์ƒ์„ฑ๋œ ์ถœ๋ ฅ ํ–‰ ์—ด์˜ Java ๋ฐ์ดํ„ฐ ํƒ€์ž…์„ ์ง€์ •ํ•˜๋Š” ์ถœ๋ ฅ ํ–‰ ํด๋ž˜์Šค ๋„ ํ•„์š”ํ•ฉ๋‹ˆ๋‹ค. ์ž์„ธํ•œ ๋‚ด์šฉ์€ ์ด ํ•ญ๋ชฉ์˜ ์ถœ๋ ฅ ํ–‰ ํด๋ž˜์Šค ์„น์…˜์— ํฌํ•จ๋˜์–ด ์žˆ์Šต๋‹ˆ๋‹ค.

๋ถ„ํ• ์— ๋Œ€ํ•œ ์‚ฌ์šฉ๋ฒ• ๋…ธํŠธยถ

  • Snowflake์—์„œ ์•”์‹œ์ ์œผ๋กœ ๋ถ„ํ• ๋œ ํ–‰์„ ์ˆ˜์‹ ํ•  ๋•Œ๋Š” ์ฒ˜๋ฆฌ๊ธฐ ์ฝ”๋“œ๊ฐ€ ํŒŒํ‹ฐ์…˜์— ๋Œ€ํ•ด ์–ด๋–ค ๊ฐ€์ •๋„ ํ•  ์ˆ˜ ์—†์Šต๋‹ˆ๋‹ค. ์•”์‹œ์  ๋ถ„ํ• ๊ณผ ํ•จ๊ป˜ ์‹คํ–‰ํ•˜๋Š” ๊ฒƒ์€ UDTF๊ฐ€ ์ถœ๋ ฅ ์ƒ์„ฑ์„ ์œ„ํ•ด ๊ฐœ๋ณ„์ ์œผ๋กœ ํ–‰๋งŒ ๋ณผ ํ•„์š”๊ฐ€ ์žˆ๊ณ  ํ–‰ ๊ฐ„์— ์ง‘๊ณ„๋œ ์ƒํƒœ๊ฐ€ ์—†๋Š” ๊ฒฝ์šฐ์— ๊ฐ€์žฅ ์œ ์šฉํ•ฉ๋‹ˆ๋‹ค. ์ด ๊ฒฝ์šฐ ์ฝ”๋“œ์—๋Š” ์•„๋งˆ ์ƒ์„ฑ์ž๋‚˜ endPartition ๋ฉ”์„œ๋“œ๊ฐ€ ํ•„์š”ํ•˜์ง€ ์•Š์„ ๊ฒƒ์ž…๋‹ˆ๋‹ค.

  • ์„ฑ๋Šฅ์„ ํ–ฅ์ƒํ•˜๊ธฐ ์œ„ํ•ด Snowflake๋Š” ์ผ๋ฐ˜์ ์œผ๋กœ UDTF ํ•ธ๋“ค๋Ÿฌ ์ฝ”๋“œ์˜ ์—ฌ๋Ÿฌ ์ธ์Šคํ„ด์Šค๋ฅผ ๋ณ‘๋ ฌ๋กœ ์‹คํ–‰ํ•ฉ๋‹ˆ๋‹ค. ํ–‰์˜ ๊ฐ ํŒŒํ‹ฐ์…˜์€ UDTF์˜ ๋‹จ์ผ ์ธ์Šคํ„ด์Šค๋กœ ์ „๋‹ฌ๋ฉ๋‹ˆ๋‹ค.

  • ๊ฐ ํŒŒํ‹ฐ์…˜์€ ํ•˜๋‚˜์˜ UDTF ์ธ์Šคํ„ด์Šค์—์„œ๋งŒ ์ฒ˜๋ฆฌ๋˜์ง€๋งŒ, ๊ทธ ๋ฐ˜๋Œ€์˜ ๊ฒฝ์šฐ์—๋„ ๋ฐ˜๋“œ์‹œ ๊ทธ๋Ÿฐ ๊ฒƒ์€ ์•„๋‹™๋‹ˆ๋‹ค. ์ฆ‰, ๋‹จ์ผ UDTF ์ธ์Šคํ„ด์Šค๋Š” ์—ฌ๋Ÿฌ ํŒŒํ‹ฐ์…˜์„ ์ˆœ์ฐจ์ ์œผ๋กœ ์ฒ˜๋ฆฌํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ๋”ฐ๋ผ์„œ ์ด๋‹ˆ์…œ๋ผ์ด์ €์™€ ํŒŒ์ด๋„๋ผ์ด์ €๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ๊ฐ ํŒŒํ‹ฐ์…˜์„ ์ดˆ๊ธฐํ™” ๋ฐ ์ •๋ฆฌํ•˜์—ฌ ํ•œ ํŒŒํ‹ฐ์…˜ ์ฒ˜๋ฆฌ์—์„œ ๋‹ค๋ฅธ ํŒŒํ‹ฐ์…˜ ์ฒ˜๋ฆฌ๋กœ ๋ˆ„์  ๊ฐ’์ด ๋„˜์–ด๊ฐ€์ง€ ์•Š๋„๋ก ํ•˜๋Š” ๊ฒƒ์ด ์ค‘์š”ํ•ฉ๋‹ˆ๋‹ค.

์ฐธ๊ณ 

ํ…Œ์ด๋ธ” ํ˜•์‹ ํ•จ์ˆ˜(UDTF)์˜ ์ž…๋ ฅ ์ธ์ž 500๊ฐœ, ์ถœ๋ ฅ ์—ด 500๊ฐœ๋กœ ์ œํ•œ๋ฉ๋‹ˆ๋‹ค.

UDTFs์šฉ Java ํด๋ž˜์Šคยถ

UDTF์˜ ๊ธฐ๋ณธ ๊ตฌ์„ฑ ์š”์†Œ๋Š” ํ•ธ๋“ค๋Ÿฌ ํด๋ž˜์Šค์™€ ์ถœ๋ ฅ ํ–‰ ํด๋ž˜์Šค์ž…๋‹ˆ๋‹ค.

์ฒ˜๋ฆฌ๊ธฐ ํด๋ž˜์Šคยถ

Snowflake๋Š” ์ฃผ๋กœ ํ•ธ๋“ค๋Ÿฌ ํด๋ž˜์Šค์˜ ๋‹ค์Œ ๋ฉ”์„œ๋“œ๋ฅผ ํ˜ธ์ถœํ•˜์—ฌ UDTF์™€ ์ƒํ˜ธ ์ž‘์šฉํ•ฉ๋‹ˆ๋‹ค.

  • ์ด๋‹ˆ์…œ๋ผ์ด์ €(์ƒ์„ฑ์ž).

  • ํ–‰๋ณ„ ๋ฉ”์„œ๋“œ(process).

  • ํŒŒ์ด๋„๋ผ์ด์ € ๋ฉ”์„œ๋“œ(endPartition).

ํ•ธ๋“ค๋Ÿฌ ํด๋ž˜์Šค๋Š” ์ด ์„ธ ๊ฐ€์ง€ ๋ฉ”์„œ๋“œ๋ฅผ ์ง€์›ํ•˜๋Š” ๋ฐ ํ•„์š”ํ•œ ์ถ”๊ฐ€ ๋ฉ”์„œ๋“œ๋ฅผ ํฌํ•จํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

ํ•ธ๋“ค๋Ÿฌ ํด๋ž˜์Šค์—๋Š” getOutputClass ๋ฉ”์„œ๋“œ๋„ ํฌํ•จ๋˜๋ฉฐ, ์ด๋Š” ๋‚˜์ค‘์— ์„ค๋ช…ํ•ฉ๋‹ˆ๋‹ค.

ํ•ธ๋“ค๋Ÿฌ ํด๋ž˜์Šค(๋˜๋Š” ์ถœ๋ ฅ ํ–‰ ํด๋ž˜์Šค)์˜ ๋ฉ”์„œ๋“œ์—์„œ ์˜ˆ์™ธ๊ฐ€ ๋ฐœ์ƒํ•˜๋ฉด ์ฒ˜๋ฆฌ๊ฐ€ ์ค‘์ง€๋ฉ๋‹ˆ๋‹ค. UDTF๋ฅผ ํ˜ธ์ถœํ•œ ์ฟผ๋ฆฌ๋Š” ์˜ค๋ฅ˜ ๋ฉ”์‹œ์ง€์™€ ํ•จ๊ป˜ ์‹คํŒจํ•ฉ๋‹ˆ๋‹ค.

์ƒ์„ฑ์žยถ

ํ•ธ๋“ค๋Ÿฌ ํด๋ž˜์Šค๋Š” ์ƒ์„ฑ์ž๋ฅผ ๊ฐ€์งˆ ์ˆ˜ ์žˆ์œผ๋ฉฐ, ์ด๋Š” 0๊ฐœ์˜ ์ธ์ž๋ฅผ ์ทจํ•ด์•ผ ํ•ฉ๋‹ˆ๋‹ค.

์ƒ์„ฑ์ž๋Š” process ํ˜ธ์ถœ ์ „์— ๊ฐ ํŒŒํ‹ฐ์…˜ ์— ๋Œ€ํ•ด ํ•œ ๋ฒˆ์”ฉ ํ˜ธ์ถœ๋ฉ๋‹ˆ๋‹ค.

์ƒ์„ฑ์ž๋Š” ์ถœ๋ ฅ ํ–‰์„ ์ƒ์„ฑํ•  ์ˆ˜ ์—†์Šต๋‹ˆ๋‹ค.

์ƒ์„ฑ์ž๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ํŒŒํ‹ฐ์…˜์˜ ์ƒํƒœ๋ฅผ ์ดˆ๊ธฐํ™”ํ•ฉ๋‹ˆ๋‹ค. ์ด ์ƒํƒœ๋Š” process ๋ฐ endPartition ๋ฉ”์„œ๋“œ์—์„œ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ์ƒ์„ฑ์ž๋Š” ํ–‰๋‹น ํ•œ ๋ฒˆ์ด ์•„๋‹ˆ๋ผ ํŒŒํ‹ฐ์…˜๋‹น ํ•œ ๋ฒˆ๋งŒ ์ˆ˜ํ–‰ํ•ด์•ผ ํ•˜๋Š” ์žฅ๊ธฐ ์‹คํ–‰ ์ดˆ๊ธฐํ™”๋ฅผ ๋ฐฐ์น˜ํ•˜๊ธฐ์— ์ ์ ˆํ•œ ์œ„์น˜์ด๊ธฐ๋„ ํ•ฉ๋‹ˆ๋‹ค.

์ƒ์„ฑ์ž๋Š” ์„ ํƒ ์‚ฌํ•ญ์ž…๋‹ˆ๋‹ค.

process ๋ฉ”์„œ๋“œยถ

process ๋ฉ”์„œ๋“œ๋Š” ์ž…๋ ฅ ํŒŒํ‹ฐ์…˜์˜ ๊ฐ ํ–‰์— ๋Œ€ํ•ด ํ•œ ๋ฒˆ์”ฉ ํ˜ธ์ถœ๋ฉ๋‹ˆ๋‹ค.

UDTF์— ์ „๋‹ฌ๋œ ์ธ์ž๋Š” process ์— ์ „๋‹ฌ๋ฉ๋‹ˆ๋‹ค. ์ธ์ž ๊ฐ’์€ SQL ๋ฐ์ดํ„ฐ ํƒ€์ž…์—์„œ Java ๋ฐ์ดํ„ฐ ํƒ€์ž…์œผ๋กœ ๋ณ€ํ™˜๋ฉ๋‹ˆ๋‹ค. (SQL ๋ฐ Java ๋ฐ์ดํ„ฐ ํƒ€์ž… ๋งคํ•‘์— ๋Œ€ํ•œ ์ž์„ธํ•œ ๋‚ด์šฉ์€ SQL-Java ๋ฐ์ดํ„ฐ ํƒ€์ž… ๋งคํ•‘ ์„น์…˜์„ ์ฐธ์กฐํ•˜์‹ญ์‹œ์˜ค.)

process ๋ฉ”์„œ๋“œ์˜ ๋งค๊ฐœ ๋ณ€์ˆ˜ ์ด๋ฆ„์€ ์œ ํšจํ•œ Java ์‹๋ณ„์ž์ผ ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ์ด๋ฆ„์€ CREATE FUNCTION ๋ฌธ์— ์ง€์ •๋œ ์ด๋ฆ„๊ณผ ์ผ์น˜ํ•  ํ•„์š”๊ฐ€ ์—†์Šต๋‹ˆ๋‹ค.

ํ•ด๋‹น process ๊ฐ€ ํ˜ธ์ถœ๋  ๋•Œ๋งˆ๋‹ค 0๊ฐœ, 1๊ฐœ ๋˜๋Š” ์—ฌ๋Ÿฌ ํ–‰์„ ๋ฐ˜ํ™˜ํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

process ๋ฉ”์„œ๋“œ๊ฐ€ ๋ฐ˜ํ™˜ํ•˜๋Š” ๋ฐ์ดํ„ฐ ํƒ€์ž…์€ Stream<OutputRow> ์—ฌ์•ผ ํ•ฉ๋‹ˆ๋‹ค. ์—ฌ๊ธฐ์„œ Stream์€ java.util.stream.Stream์— ์ •์˜๋˜๊ณ  OutputRow ๋Š” ์ถœ๋ ฅ ํ–‰ ํด๋ž˜์Šค์˜ ์ด๋ฆ„์ž…๋‹ˆ๋‹ค. ์•„๋ž˜ ์˜ˆ๋Š” Stream์„ ํ†ตํ•ด ์ž…๋ ฅ์„ ๋ฐ˜ํ™˜ํ•˜๋Š” ๊ฐ„๋‹จํ•œ process ๋ฉ”์„œ๋“œ๋ฅผ ๋ณด์—ฌ์ค๋‹ˆ๋‹ค.

import java.util.stream.Stream;

...

public Stream<OutputRow> process(String v) {
  return Stream.of(new OutputRow(v));
}

...
Copy

process ๋ฉ”์„œ๋“œ๊ฐ€ ์˜ค๋ธŒ์ ํŠธ์˜ ์ƒํƒœ๋ฅผ ์œ ์ง€ํ•˜๊ฑฐ๋‚˜ ์‚ฌ์šฉํ•˜์ง€ ์•Š๋Š” ๊ฒฝ์šฐ(์˜ˆ: ์ถœ๋ ฅ์—์„œ ์„ ํƒํ•œ ์ž…๋ ฅ ํ–‰์„ ๋‹จ์ˆœํžˆ ์ œ์™ธํ•˜๋„๋ก ๋ฉ”์„œ๋“œ๊ฐ€ ์„ค๊ณ„๋œ ๊ฒฝ์šฐ) ๋ฉ”์„œ๋“œ๋ฅผ static ์œผ๋กœ ์„ ์–ธํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. process ๋ฉ”์„œ๋“œ๊ฐ€ static ์ด๊ณ  ํ•ธ๋“ค๋Ÿฌ ํด๋ž˜์Šค์— ์ƒ์„ฑ์ž ๋˜๋Š” ๋น„์ •์  endPartition ๋ฉ”์„œ๋“œ๊ฐ€ ์—†๋Š” ๊ฒฝ์šฐ Snowflake๋Š” ํ•ธ๋“ค๋Ÿฌ ํด๋ž˜์Šค์˜ ์ธ์Šคํ„ด์Šค๋ฅผ ์ƒ์„ฑํ•˜์ง€ ์•Š๊ณ  ๊ฐ ํ–‰์„ ์ •์  process ๋ฉ”์„œ๋“œ์— ์ง์ ‘ ์ „๋‹ฌํ•ฉ๋‹ˆ๋‹ค.

์ž…๋ ฅ ํ–‰์„ ๊ฑด๋„ˆ๋›ฐ๊ณ  ๋‹ค์Œ ํ–‰์„ ์ฒ˜๋ฆฌํ•ด์•ผ ํ•˜๋Š” ๊ฒฝ์šฐ(์˜ˆ: ์ž…๋ ฅ ํ–‰์˜ ์œ ํšจ์„ฑ์„ ๊ฒ€์‚ฌํ•˜๋Š” ๊ฒฝ์šฐ) ๋นˆ Stream ์˜ค๋ธŒ์ ํŠธ๋ฅผ ๋ฐ˜ํ™˜ํ•ฉ๋‹ˆ๋‹ค. ์˜ˆ๋ฅผ ๋“ค์–ด ์•„๋ž˜์˜ process ๋ฉ”์„œ๋“œ๋Š” number ๊ฐ€ ์–‘์˜ ์ •์ˆ˜์ธ ํ–‰๋งŒ ๋ฐ˜ํ™˜ํ•ฉ๋‹ˆ๋‹ค. number ๊ฐ€ ์–‘์ˆ˜๊ฐ€ ์•„๋‹Œ ๊ฒฝ์šฐ ๋ฉ”์„œ๋“œ๋Š” ํ˜„์žฌ ํ–‰์„ ๊ฑด๋„ˆ๋›ฐ๊ณ  ๋‹ค์Œ ํ–‰์„ ๊ณ„์† ์ฒ˜๋ฆฌํ•˜๊ธฐ ์œ„ํ•ด ๋นˆ Stream ์˜ค๋ธŒ์ ํŠธ๋ฅผ ๋ฐ˜ํ™˜ํ•ฉ๋‹ˆ๋‹ค.

public Stream<OutputRow> process(int number) {
  if (inputNumber < 1) {
    return Stream.empty();
  }
  return Stream.of(new OutputRow(number));
}
Copy

process ๊ฐ€ null Stream์„ ๋ฐ˜ํ™˜ํ•˜๋ฉด ์ฒ˜๋ฆฌ๊ฐ€ ์ค‘์ง€๋ฉ๋‹ˆ๋‹ค. (null Stream์ด ๋ฐ˜ํ™˜๋˜๋”๋ผ๋„ endPartition ๋ฉ”์„œ๋“œ๋Š” ๊ณ„์† ํ˜ธ์ถœ๋ฉ๋‹ˆ๋‹ค.)

์ด ๋ฉ”์„œ๋“œ๋Š” ํ•„์ˆ˜์ž…๋‹ˆ๋‹ค.

endPartition ๋ฉ”์„œ๋“œยถ

์ด ์„ ํƒ์  ๋ฉ”์„œ๋“œ๋Š” process ์—์„œ ์ง‘๊ณ„๋œ ์ƒํƒœ ์ •๋ณด๋ฅผ ๊ธฐ๋ฐ˜์œผ๋กœ ํ•˜๋Š” ์ถœ๋ ฅ ํ–‰์„ ์ƒ์„ฑํ•˜๋Š” ๋ฐ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ์ด ๋ฉ”์„œ๋“œ๋Š” ํ•ด๋‹น ํŒŒํ‹ฐ์…˜์˜ ๋ชจ๋“  ํ–‰์ด process ์— ์ „๋‹ฌ๋œ ํ›„ ๊ฐ ํŒŒํ‹ฐ์…˜ ์— ๋Œ€ํ•ด ํ•œ ๋ฒˆ ํ˜ธ์ถœ๋ฉ๋‹ˆ๋‹ค.

์ด ๋ฉ”์„œ๋“œ๋ฅผ ํฌํ•จํ•˜๋ฉด ๋ฐ์ดํ„ฐ๊ฐ€ ๋ช…์‹œ์ ์œผ๋กœ ๋˜๋Š” ์•”์‹œ์ ์œผ๋กœ ๋ถ„ํ• ๋˜์—ˆ๋Š”์ง€ ์—ฌ๋ถ€์— ๊ด€๊ณ„์—†์ด ๊ฐ ํŒŒํ‹ฐ์…˜์—์„œ ํ˜ธ์ถœ๋ฉ๋‹ˆ๋‹ค. ๋ฐ์ดํ„ฐ๊ฐ€ ์˜๋ฏธ ์žˆ๊ฒŒ ๋ถ„ํ• ๋˜์ง€ ์•Š์œผ๋ฉด ํŒŒ์ด๋„๋ผ์ด์ €์˜ ์ถœ๋ ฅ์ด ์˜๋ฏธ๊ฐ€ ์—†์„ ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

์ฐธ๊ณ 

์‚ฌ์šฉ์ž๊ฐ€ ๋ฐ์ดํ„ฐ๋ฅผ ๋ช…์‹œ์ ์œผ๋กœ ๋ถ„ํ• ํ•˜์ง€ ์•Š์œผ๋ฉด Snowflake๋Š” ๋ฐ์ดํ„ฐ๋ฅผ ์•”์‹œ์ ์œผ๋กœ ๋ถ„ํ• ํ•ฉ๋‹ˆ๋‹ค. ์ž์„ธํ•œ ๋‚ด์šฉ์€ ํŒŒํ‹ฐ์…˜ ์„ ์ฐธ์กฐํ•˜์‹ญ์‹œ์˜ค.

์ด ๋ฉ”์„œ๋“œ๋Š” 0๊ฐœ, 1๊ฐœ ๋˜๋Š” ์—ฌ๋Ÿฌ ํ–‰์„ ์ถœ๋ ฅํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

์ฐธ๊ณ 

Snowflake๋Š” ์„ฑ๊ณต์ ์œผ๋กœ ์ฒ˜๋ฆฌํ•˜๋„๋ก ์‹œ๊ฐ„ ์ œํ•œ์ด ์กฐ์ •๋œ ๋Œ€ํ˜• ํŒŒํ‹ฐ์…˜์„ ์ง€์›ํ•˜์ง€๋งŒ, ํŠนํžˆ ๋Œ€ํ˜• ํŒŒํ‹ฐ์…˜์œผ๋กœ ์ธํ•ด ์ฒ˜๋ฆฌ ์‹œ๊ฐ„์ด ์ดˆ๊ณผ๋  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค(์˜ˆ: endPartition ์ด ์™„๋ฃŒํ•˜๋Š” ๋ฐ ๋„ˆ๋ฌด ์˜ค๋ž˜ ๊ฑธ๋ฆฌ๋Š” ๊ฒฝ์šฐ). ํŠน์ • ์‚ฌ์šฉ ์‹œ๋‚˜๋ฆฌ์˜ค์— ๋งž๊ฒŒ ์‹œ๊ฐ„ ์ดˆ๊ณผ ์ž„๊ณ„๊ฐ’์„ ์กฐ์ •ํ•ด์•ผ ํ•˜๋Š” ๊ฒฝ์šฐ Snowflake ์ง€์› ์— ๋ฌธ์˜ํ•˜์‹ญ์‹œ์˜ค.

getOutputClass ๋ฉ”์„œ๋“œยถ

์ด ๋ฉ”์„œ๋“œ๋Š” ์ถœ๋ ฅ ํ–‰ ํด๋ž˜์Šค ์— ๋Œ€ํ•œ ์ •๋ณด๋ฅผ ๋ฐ˜ํ™˜ํ•ฉ๋‹ˆ๋‹ค. ์ถœ๋ ฅ ํ–‰ ํด๋ž˜์Šค์—๋Š” ๋ฐ˜ํ™˜๋œ ํ–‰์˜ ๋ฐ์ดํ„ฐ ํƒ€์ž…์— ๋Œ€ํ•œ ์ •๋ณด๊ฐ€ ํฌํ•จ๋ฉ๋‹ˆ๋‹ค.

์ถœ๋ ฅ ํ–‰ ํด๋ž˜์Šคยถ

Snowflake๋Š” ์ถœ๋ ฅ ํ–‰ ํด๋ž˜์Šค๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ Java ๋ฐ์ดํ„ฐ ํƒ€์ž…๊ณผ SQL ๋ฐ์ดํ„ฐ ํƒ€์ž… ๊ฐ„์˜ ๋ณ€ํ™˜์„ ์ง€์ •ํ•˜๋Š” ๋ฐ ๋„์›€์„ ์ค๋‹ˆ๋‹ค.

Java UDTF๊ฐ€ ํ–‰์„ ๋ฐ˜ํ™˜ํ•  ๋•Œ ํ–‰์˜ ๊ฐ ์—ด์— ์žˆ๋Š” ๊ฐ’์€ Java ๋ฐ์ดํ„ฐ ํƒ€์ž…์—์„œ ํ•ด๋‹น SQL ๋ฐ์ดํ„ฐ ํƒ€์ž…์œผ๋กœ ๋ณ€ํ™˜๋˜์–ด์•ผ ํ•ฉ๋‹ˆ๋‹ค. SQL ๋ฐ์ดํ„ฐ ํƒ€์ž…์€ CREATE FUNCTION ๋ฌธ์˜ RETURNS ์ ˆ์— ์ง€์ •๋ฉ๋‹ˆ๋‹ค. ๊ทธ๋Ÿฌ๋‚˜ Java ๋ฐ SQL ๋ฐ์ดํ„ฐ ํƒ€์ž… ๊ฐ„์˜ ๋งคํ•‘์€ 1:1์ด ์•„๋‹ˆ๋ฏ€๋กœ Snowflake๋Š” ๋ฐ˜ํ™˜๋œ ๊ฐ ์—ด์— ๋Œ€ํ•œ Java ๋ฐ์ดํ„ฐ ํƒ€์ž…์„ ์•Œ์•„์•ผ ํ•ฉ๋‹ˆ๋‹ค. (SQL ๋ฐ Java ๋ฐ์ดํ„ฐ ํƒ€์ž… ๋งคํ•‘์— ๋Œ€ํ•œ ์ž์„ธํ•œ ๋‚ด์šฉ์€ SQL-Java ๋ฐ์ดํ„ฐ ํƒ€์ž… ๋งคํ•‘ ์„น์…˜์„ ์ฐธ์กฐํ•˜์‹ญ์‹œ์˜ค.)

Java UDTF๋Š” ์ถœ๋ ฅ ํ–‰ ํด๋ž˜์Šค๋ฅผ ์ •์˜ํ•˜์—ฌ ์ถœ๋ ฅ ์—ด์˜ Java ๋ฐ์ดํ„ฐ ํƒ€์ž…์„ ์ง€์ •ํ•ฉ๋‹ˆ๋‹ค. UDTF์—์„œ ๋ฐ˜ํ™˜๋œ ๊ฐ ํ–‰์€ ์ถœ๋ ฅ ํ–‰ ํด๋ž˜์Šค์˜ ์ธ์Šคํ„ด์Šค๋กœ ๋ฐ˜ํ™˜๋ฉ๋‹ˆ๋‹ค. ์ถœ๋ ฅ ํ–‰ ํด๋ž˜์Šค์˜ ๊ฐ ์ธ์Šคํ„ด์Šค์—๋Š” ๊ฐ ์ถœ๋ ฅ ์—ด์— ๋Œ€ํ•ด ํ•˜๋‚˜์˜ ๊ณต์šฉ ํ•„๋“œ๊ฐ€ ์žˆ์Šต๋‹ˆ๋‹ค. Snowflake๋Š” ์ถœ๋ ฅ ํ–‰ ํด๋ž˜์Šค์˜ ๊ฐ ์ธ์Šคํ„ด์Šค์—์„œ ๊ณต์šฉ ํ•„๋“œ ๊ฐ’์„ ์ฝ๊ณ , Java ๊ฐ’์„ SQL ๊ฐ’์œผ๋กœ ๋ณ€ํ™˜ํ•˜๊ณ , ํ•ด๋‹น ๊ฐ’์„ ํฌํ•จํ•˜๋Š” SQL ์ถœ๋ ฅ ํ–‰์„ ๊ตฌ์„ฑํ•ฉ๋‹ˆ๋‹ค.

์ถœ๋ ฅ ํ–‰ ํด๋ž˜์Šค์˜ ๊ฐ ์ธ์Šคํ„ด์Šค์— ์žˆ๋Š” ๊ฐ’์€ ์ถœ๋ ฅ ํ–‰ ํด๋ž˜์Šค์˜ ์ƒ์„ฑ์ž๋ฅผ ํ˜ธ์ถœํ•˜์—ฌ ์„ค์ •๋ฉ๋‹ˆ๋‹ค. ์ƒ์„ฑ์ž๋Š” ์ถœ๋ ฅ ์—ด์— ํ•ด๋‹นํ•˜๋Š” ๋งค๊ฐœ ๋ณ€์ˆ˜๋ฅผ ์ˆ˜๋ฝํ•œ ๋‹ค์Œ ๊ณต์šฉ ํ•„๋“œ๋ฅผ ํ•ด๋‹น ๋งค๊ฐœ ๋ณ€์ˆ˜๋กœ ์„ค์ •ํ•ฉ๋‹ˆ๋‹ค.

์•„๋ž˜ ์ฝ”๋“œ๋Š” ์ƒ˜ํ”Œ ์ถœ๋ ฅ ํ–‰ ํด๋ž˜์Šค๋ฅผ ์ •์˜ํ•ฉ๋‹ˆ๋‹ค.

class OutputRow {

  public String name;
  public int id;

  public OutputRow(String pName, int pId) {
    this.name = pName;
    this.id = pId;
  }

}
Copy

์ด ํด๋ž˜์Šค์— ์˜ํ•ด ์ง€์ •๋œ ๊ณต์šฉ ๋ณ€์ˆ˜๋Š” CREATE FUNCTION ๋ฌธ์˜ RETURNS TABLE (...) ์ ˆ์— ์ง€์ •๋œ ์—ด๊ณผ ์ผ์น˜ํ•ด์•ผ ํ•ฉ๋‹ˆ๋‹ค. ์˜ˆ๋ฅผ ๋“ค์–ด ์œ„์˜ OutputRow ํด๋ž˜์Šค๋Š” ์•„๋ž˜์˜ RETURNS ์ ˆ์— ํ•ด๋‹นํ•ฉ๋‹ˆ๋‹ค.

CREATE FUNCTION F(...)
  RETURNS TABLE(NAME VARCHAR, ID INTEGER)
  ...
Copy

์ค‘์š”

SQL ์—ด ์ด๋ฆ„๊ณผ ์ถœ๋ ฅ ํ–‰ ํด๋ž˜์Šค์˜ Java ๊ณต์šฉ ํ•„๋“œ ์ด๋ฆ„ ๊ฐ„์˜ ์ผ์น˜๋Š” ๋Œ€/์†Œ๋ฌธ์ž๋ฅผ ๊ตฌ๋ถ„ํ•˜์ง€ ์•Š์Šต๋‹ˆ๋‹ค. ์˜ˆ๋ฅผ ๋“ค์–ด, ์œ„์— ํ‘œ์‹œ๋œ Java ๋ฐ SQL ์ฝ”๋“œ์—์„œ id ๋ผ๋Š” Java ํ•„๋“œ๋Š” ID ๋ผ๋Š” SQL ์—ด์— ํ•ด๋‹นํ•ฉ๋‹ˆ๋‹ค.

์ถœ๋ ฅ ํ–‰ ํด๋ž˜์Šค๋Š” ๋‹ค์Œ๊ณผ ๊ฐ™์ด ์‚ฌ์šฉ๋ฉ๋‹ˆ๋‹ค.

  • ํ•ธ๋“ค๋Ÿฌ ํด๋ž˜์Šค๋Š” ์ถœ๋ ฅ ํ–‰ ํด๋ž˜์Šค๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ process ๋ฉ”์„œ๋“œ์™€ endPartition ๋ฉ”์„œ๋“œ์˜ ๋ฐ˜ํ™˜ ํƒ€์ž…์„ ์ง€์ •ํ•ฉ๋‹ˆ๋‹ค. ํ•ธ๋“ค๋Ÿฌ ํด๋ž˜์Šค๋Š” ๋˜ํ•œ ์ถœ๋ ฅ ํ–‰ ํด๋ž˜์Šค๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ๋ฐ˜ํ™˜๋œ ๊ฐ’์„ ๊ตฌ์„ฑํ•ฉ๋‹ˆ๋‹ค. ์˜ˆ:

    public Stream<OutputRow> process(String v) {
      ...
      return Stream.of(new OutputRow(...));
    }
    
    public Stream<OutputRow> endPartition() {
      ...
      return Stream.of(new OutputRow(...));
    }
    
    Copy
  • ์ถœ๋ ฅ ํ–‰ ํด๋ž˜์Šค๋Š” ํ•ธ๋“ค๋Ÿฌ ํด๋ž˜์Šค์˜ getOutputClass ๋ฉ”์„œ๋“œ์—์„œ๋„ ์‚ฌ์šฉ๋ฉ๋‹ˆ๋‹ค. ์ด๋Š” Snowflake๊ฐ€ ์ถœ๋ ฅ์˜ Java ๋ฐ์ดํ„ฐ ํƒ€์ž…์„ ํ•™์Šตํ•˜๊ธฐ ์œ„ํ•ด ํ˜ธ์ถœํ•˜๋Š” ์ •์  ๋ฉ”์„œ๋“œ์ž…๋‹ˆ๋‹ค.

    public static Class getOutputClass() {
      return OutputRow.class;
    }
    
    Copy

์ถœ๋ ฅ ํ–‰ ํด๋ž˜์Šค(๋˜๋Š” ์ฒ˜๋ฆฌ๊ธฐ ํด๋ž˜์Šค)์˜ ๋ฉ”์„œ๋“œ์—์„œ ์˜ˆ์™ธ๊ฐ€ ๋ฐœ์ƒํ•˜๋ฉด ์ฒ˜๋ฆฌ๊ฐ€ ์ค‘์ง€๋ฉ๋‹ˆ๋‹ค. UDTF๋ฅผ ํ˜ธ์ถœํ•œ ์ฟผ๋ฆฌ๋Š” ์˜ค๋ฅ˜ ๋ฉ”์‹œ์ง€์™€ ํ•จ๊ป˜ ์‹คํŒจํ•ฉ๋‹ˆ๋‹ค.

์š”๊ตฌ ์‚ฌํ•ญ ์š”์•ฝยถ

UDTF์˜ Java ์ฝ”๋“œ๋Š” ๋‹ค์Œ ์š”๊ตฌ ์‚ฌํ•ญ์„ ์ถฉ์กฑํ•ด์•ผ ํ•ฉ๋‹ˆ๋‹ค.

  • ์ฝ”๋“œ๋Š” ์ถœ๋ ฅ ํ–‰ ํด๋ž˜์Šค ๋ฅผ ์ •์˜ํ•ด์•ผ ํ•ฉ๋‹ˆ๋‹ค.

  • UDTF ํ•ธ๋“ค๋Ÿฌ ํด๋ž˜์Šค์—๋Š” <์ถœ๋ ฅ_ํ–‰_ํด๋ž˜์Šค> ์˜ Stream์„ ๋ฐ˜ํ™˜ํ•˜๋Š” process ๋ผ๋Š” ๊ณต์šฉ ๋ฉ”์„œ๋“œ๊ฐ€ ํฌํ•จ๋˜์–ด์•ผ ํ•ฉ๋‹ˆ๋‹ค. ์—ฌ๊ธฐ์„œ Stream์€ java.util.stream.Stream์— ์ •์˜๋˜์–ด ์žˆ์Šต๋‹ˆ๋‹ค.

  • UDTF ํ•ธ๋“ค๋Ÿฌ ํด๋ž˜์Šค๋Š” <์ถœ๋ ฅ_ํ–‰_ํด๋ž˜์Šค>.class ๋ฅผ ๋ฐ˜ํ™˜ํ•ด์•ผ ํ•˜๋Š” getOutputClass ๋ผ๋Š” ๊ณต์šฉ ์ •์  ๋ฉ”์„œ๋“œ๋ฅผ ์ •์˜ํ•ด์•ผ ํ•ฉ๋‹ˆ๋‹ค.

Java ์ฝ”๋“œ๊ฐ€ ์ด๋Ÿฌํ•œ ์š”๊ตฌ ์‚ฌํ•ญ์„ ์ถฉ์กฑํ•˜์ง€ ์•Š์œผ๋ฉด UDTF ์ƒ์„ฑ ๋˜๋Š” ์‹คํ–‰์ด ์‹คํŒจํ•ฉ๋‹ˆ๋‹ค.

  • CREATE FUNCTION ๋ฌธ์ด ์‹คํ–‰๋  ๋•Œ ์„ธ์…˜์— ํ™œ์„ฑ ์›จ์–ดํ•˜์šฐ์Šค๊ฐ€ ์žˆ๋Š” ๊ฒฝ์šฐ Snowflake๋Š” ํ•จ์ˆ˜๊ฐ€ ์ƒ์„ฑ๋  ๋•Œ ์œ„๋ฐ˜์„ ๊ฐ์ง€ํ•ฉ๋‹ˆ๋‹ค.

  • CREATE FUNCTION ๋ฌธ์ด ์‹คํ–‰๋  ๋•Œ ์„ธ์…˜์— ํ™œ์„ฑ ์›จ์–ดํ•˜์šฐ์Šค๊ฐ€ ์—†์œผ๋ฉด Snowflake๋Š” ํ•จ์ˆ˜๊ฐ€ ํ˜ธ์ถœ๋  ๋•Œ ์œ„๋ฐ˜์„ ๊ฐ์ง€ํ•ฉ๋‹ˆ๋‹ค.

์ฟผ๋ฆฌ์—์„œ Java UDTFs ํ˜ธ์ถœ์˜ ์˜ˆยถ

UDF ๋ฐ UDTF ํ˜ธ์ถœ์— ๋Œ€ํ•œ ์ผ๋ฐ˜ ์ •๋ณด๋Š” UDF ์‹คํ–‰ํ•˜๊ธฐ ์„น์…˜์„ ์ฐธ์กฐํ•˜์‹ญ์‹œ์˜ค.

๋ช…์‹œ์  ๋ถ„ํ•  ์—†์ด ํ˜ธ์ถœํ•˜๊ธฐยถ

์ด ์˜ˆ๋Š” UDTF๋ฅผ ๋งŒ๋“œ๋Š” ๋ฐฉ๋ฒ•์„ ๋ณด์—ฌ์ค๋‹ˆ๋‹ค. ์ด ์˜ˆ๋Š” ๊ฐ ์ž…๋ ฅ์˜ ๋‘ ๋ณต์‚ฌ๋ณธ์„ ๋ฐ˜ํ™˜ํ•˜๊ณ  ๊ฐ ํŒŒํ‹ฐ์…˜์— ๋Œ€ํ•ด ํ•˜๋‚˜์˜ ์ถ”๊ฐ€ ํ–‰์„ ๋ฐ˜ํ™˜ํ•ฉ๋‹ˆ๋‹ค.

create function return_two_copies(v varchar)
returns table(output_value varchar)
language java
handler='TestFunction'
target_path='@~/TestFunction.jar'
as
$$

  import java.util.stream.Stream;

  class OutputRow {

    public String output_value;

    public OutputRow(String outputValue) {
      this.output_value = outputValue;
    }

  }


  class TestFunction {

    String myString;

    public TestFunction()  {
      myString = "Created in constructor and output from endPartition()";
    }

    public static Class getOutputClass() {
      return OutputRow.class;
    }

    public Stream<OutputRow> process(String inputValue) {
      // Return two rows with the same value.
      return Stream.of(new OutputRow(inputValue), new OutputRow(inputValue));
    }

    public Stream<OutputRow> endPartition() {
      // Returns the value we initialized in the constructor.
      return Stream.of(new OutputRow(myString));
    }

  }

$$;
Copy

์ด ์˜ˆ๋Š” UDTF๋ฅผ ํ˜ธ์ถœํ•˜๋Š” ๋ฐฉ๋ฒ•์„ ๋ณด์—ฌ์ค๋‹ˆ๋‹ค. ์ด ์˜ˆ๋ฅผ ๋‹จ์ˆœํ•˜๊ฒŒ ์œ ์ง€ํ•˜๊ธฐ ์œ„ํ•ด ๋ฌธ์€ ์—ด์ด ์•„๋‹Œ ๋ฆฌํ„ฐ๋Ÿด ๊ฐ’์„ ์ „๋‹ฌํ•˜๊ณ  OVER() ์ ˆ์„ ์ƒ๋žตํ•ฉ๋‹ˆ๋‹ค.

SELECT output_value
   FROM TABLE(return_two_copies('Input string'));
+-------------------------------------------------------+
| OUTPUT_VALUE                                          |
|-------------------------------------------------------|
| Input string                                          |
| Input string                                          |
| Created in constructor and output from endPartition() |
+-------------------------------------------------------+
Copy

์ด ์˜ˆ์—์„œ๋Š” ๋‹ค๋ฅธ ํ…Œ์ด๋ธ”์— ์ฝ์€ ๊ฐ’์œผ๋กœ UDTF๋ฅผ ํ˜ธ์ถœํ•ฉ๋‹ˆ๋‹ค. process ๋ฉ”์„œ๋“œ๊ฐ€ ํ˜ธ์ถœ๋  ๋•Œ๋งˆ๋‹ค cities_of_interest ํ…Œ์ด๋ธ”์˜ ํ˜„์žฌ ํ–‰์— ์žˆ๋Š” city_name ์—ด์˜ ๊ฐ’์ด ์ „๋‹ฌ๋ฉ๋‹ˆ๋‹ค. ์œ„์™€ ๊ฐ™์ด, UDTF๋Š” ๋ช…์‹œ์  OVER() ์ ˆ ์—†์ด ํ˜ธ์ถœ๋ฉ๋‹ˆ๋‹ค.

์ž…๋ ฅ ์†Œ์Šค๋กœ ์‚ฌ์šฉํ•  ๊ฐ„๋‹จํ•œ ํ…Œ์ด๋ธ”์„ ๋งŒ๋“ญ๋‹ˆ๋‹ค.

CREATE TABLE cities_of_interest (city_name VARCHAR);
INSERT INTO cities_of_interest (city_name) VALUES
    ('Toronto'),
    ('Warsaw'),
    ('Kyoto');
Copy

Java UDTF ํ˜ธ์ถœ:

SELECT city_name, output_value
   FROM cities_of_interest,
       TABLE(return_two_copies(city_name))
   ORDER BY city_name, output_value;
+-----------+-------------------------------------------------------+
| CITY_NAME | OUTPUT_VALUE                                          |
|-----------+-------------------------------------------------------|
| Kyoto     | Kyoto                                                 |
| Kyoto     | Kyoto                                                 |
| Toronto   | Toronto                                               |
| Toronto   | Toronto                                               |
| Warsaw    | Warsaw                                                |
| Warsaw    | Warsaw                                                |
| NULL      | Created in constructor and output from endPartition() |
+-----------+-------------------------------------------------------+
Copy

์ฃผ์˜

์ด ์˜ˆ์—์„œ FROM ์ ˆ์— ์‚ฌ์šฉ๋œ ๊ตฌ๋ฌธ์€ ๋‚ด๋ถ€ ์กฐ์ธ์˜ ๊ตฌ๋ฌธ(์ฆ‰, FROM t1, t2)๊ณผ ๋™์ผํ•ฉ๋‹ˆ๋‹ค. ๊ทธ๋Ÿฌ๋‚˜ ์ˆ˜ํ–‰๋œ ์ž‘์—…์€ ์‹ค์ œ ๋‚ด๋ถ€ ์กฐ์ธ์ด ์•„๋‹™๋‹ˆ๋‹ค. ์‹ค์ œ ๋™์ž‘์€ ํ•จ์ˆ˜๊ฐ€ ํ…Œ์ด๋ธ”์— ์žˆ๋Š” ๊ฐ ํ–‰์˜ ๊ฐ’์œผ๋กœ ํ˜ธ์ถœ๋œ๋‹ค๋Š” ๊ฒƒ์ž…๋‹ˆ๋‹ค. ์ฆ‰, ๋‹ค์Œ FROM ์ ˆ์ด ์ฃผ์–ด์กŒ์„ ๋•Œ:

FROM cities_of_interest, TABLE(f(city_name))
Copy

๋™์ž‘์€ ๋‹ค์Œ ์˜์‚ฌ ์ฝ”๋“œ์™€ ๋™์ผํ•ฉ๋‹ˆ๋‹ค.

for city_name in cities_of_interest:
    output_row = f(city_name)
Copy

JavaScript UDTF์— ๋Œ€ํ•œ ์„ค๋ช…์„œ์˜ ์˜ˆ ์„น์…˜ ์—๋Š” ํ…Œ์ด๋ธ”์˜ ๊ฐ’์œผ๋กœ UDTF๋ฅผ ํ˜ธ์ถœํ•˜๋Š” ์ฟผ๋ฆฌ์˜ ๋” ๋ณต์žกํ•œ ์˜ˆ๊ฐ€ ํฌํ•จ๋˜์–ด ์žˆ์Šต๋‹ˆ๋‹ค.

์ด ๋ฌธ์ด ๋ถ„ํ• ์„ ๋ช…์‹œ์ ์œผ๋กœ ์ง€์ •ํ•˜์ง€ ์•Š์€ ๊ฒฝ์šฐ์—๋Š” Snowflake ์‹คํ–‰ ์—”์ง„์€ ์•”์‹œ์  ๋ถ„ํ•  ์„ ์‚ฌ์šฉํ•ฉ๋‹ˆ๋‹ค.

ํŒŒํ‹ฐ์…˜์ด ํ•˜๋‚˜๋งŒ ์žˆ๋Š” ๊ฒฝ์šฐ endPartition ๋ฉ”์„œ๋“œ๋Š” ํ•œ ๋ฒˆ๋งŒ ํ˜ธ์ถœ๋˜๊ณ  ์ฟผ๋ฆฌ ์ถœ๋ ฅ์—๋Š” Created in constructor and output from endPartition() ๊ฐ’์ด ์žˆ๋Š” ํ–‰์ด ํ•˜๋‚˜๋งŒ ํฌํ•จ๋ฉ๋‹ˆ๋‹ค. ๋ฌธ์„ ๋‹ค๋ฅด๊ฒŒ ์‹คํ–‰ํ•˜๋Š” ์ค‘์— ๋ฐ์ดํ„ฐ๊ฐ€ ๋‹ค๋ฅธ ๊ฐœ์ˆ˜์˜ ํŒŒํ‹ฐ์…˜์œผ๋กœ ๊ทธ๋ฃนํ™”๋˜๋ฉด endPartition ๋ฉ”์„œ๋“œ๊ฐ€ ๋‹ค๋ฅธ ํšŸ์ˆ˜๋กœ ํ˜ธ์ถœ๋˜๊ณ  ์ถœ๋ ฅ์—๋Š” ์ด ํ–‰์˜ ๋ณต์‚ฌ๋ณธ์ด ํฌํ•จ๋˜๋Š”๋ฐ ๊ทธ ๊ฐœ์ˆ˜๊ฐ€ ๋‹ค๋ฆ…๋‹ˆ๋‹ค.

์ž์„ธํ•œ ๋‚ด์šฉ์€ ์•”์‹œ์  ๋ถ„ํ•  ์„ ์ฐธ์กฐํ•˜์‹ญ์‹œ์˜ค.

๋ช…์‹œ์  ๋ถ„ํ• ๋กœ ํ˜ธ์ถœํ•˜๊ธฐยถ

Java UDTF๋Š” ๋ช…์‹œ์  ๋ถ„ํ• ์„ ์‚ฌ์šฉํ•˜์—ฌ ํ˜ธ์ถœํ•  ์ˆ˜๋„ ์žˆ์Šต๋‹ˆ๋‹ค.

๋‹ค์ค‘ ํŒŒํ‹ฐ์…˜ยถ

๋‹ค์Œ ์˜ˆ์—์„œ๋Š” ์ด์ „์— ๋งŒ๋“  ๋™์ผํ•œ UDTF ๋ฐ ํ…Œ์ด๋ธ”์„ ์‚ฌ์šฉํ•ฉ๋‹ˆ๋‹ค. ์ด ์˜ˆ์—์„œ๋Š” city_name์„ ๊ธฐ์ค€์œผ๋กœ ๋ฐ์ดํ„ฐ๋ฅผ ๋ถ„ํ• ํ•ฉ๋‹ˆ๋‹ค.

SELECT city_name, output_value
   FROM cities_of_interest,
       TABLE(return_two_copies(city_name) OVER (PARTITION BY city_name))
   ORDER BY city_name, output_value;
+-----------+-------------------------------------------------------+
| CITY_NAME | OUTPUT_VALUE                                          |
|-----------+-------------------------------------------------------|
| Kyoto     | Created in constructor and output from endPartition() |
| Kyoto     | Kyoto                                                 |
| Kyoto     | Kyoto                                                 |
| Toronto   | Created in constructor and output from endPartition() |
| Toronto   | Toronto                                               |
| Toronto   | Toronto                                               |
| Warsaw    | Created in constructor and output from endPartition() |
| Warsaw    | Warsaw                                                |
| Warsaw    | Warsaw                                                |
+-----------+-------------------------------------------------------+
Copy

๋‹จ์ผ ํŒŒํ‹ฐ์…˜ยถ

๋‹ค์Œ ์˜ˆ์—์„œ๋Š” ์ด์ „์— ๋งŒ๋“  ๋™์ผํ•œ UDTF ๋ฐ ํ…Œ์ด๋ธ”์„ ์‚ฌ์šฉํ•˜๊ณ , ์ƒ์ˆ˜๋กœ ๋ฐ์ดํ„ฐ๋ฅผ ๋ถ„ํ• ํ•˜๋ฏ€๋กœ Snowflake๋Š” ๋‹จ์ผ ๋ถ„ํ• ๋งŒ ์‚ฌ์šฉํ•˜๊ฒŒ ๋ฉ๋‹ˆ๋‹ค.

SELECT city_name, output_value
   FROM cities_of_interest,
       TABLE(return_two_copies(city_name) OVER (PARTITION BY 1))
   ORDER BY city_name, output_value;
+-----------+-------------------------------------------------------+
| CITY_NAME | OUTPUT_VALUE                                          |
|-----------+-------------------------------------------------------|
| Kyoto     | Kyoto                                                 |
| Kyoto     | Kyoto                                                 |
| Toronto   | Toronto                                               |
| Toronto   | Toronto                                               |
| Warsaw    | Warsaw                                                |
| Warsaw    | Warsaw                                                |
| NULL      | Created in constructor and output from endPartition() |
+-----------+-------------------------------------------------------+
Copy

๋ฉ”์‹œ์ง€ Created in constructor and output from endPartition() ์˜ ๋ณต์‚ฌ๋ณธ ํ•˜๋‚˜๋งŒ ์ถœ๋ ฅ์— ํฌํ•จ๋˜์—ˆ์œผ๋ฉฐ ์ด๋Š” endPartition ์ด ํ•œ ๋ฒˆ๋งŒ ํ˜ธ์ถœ๋˜์—ˆ์Œ์„ ๋‚˜ํƒ€๋ƒ…๋‹ˆ๋‹ค.

๋งค์šฐ ํฐ ์ž…๋ ฅ(์˜ˆ: ํฐ ํŒŒ์ผ) ์ฒ˜๋ฆฌํ•˜๊ธฐยถ

์–ด๋–ค ๊ฒฝ์šฐ์—๋Š” UDTF๊ฐ€ ๊ฐ ์ž…๋ ฅ ํ–‰์„ ์ฒ˜๋ฆฌํ•˜๋Š” ๋ฐ ๋งค์šฐ ๋งŽ์€ ์–‘์˜ ๋ฉ”๋ชจ๋ฆฌ๊ฐ€ ํ•„์š”ํ•ฉ๋‹ˆ๋‹ค. ์˜ˆ๋ฅผ ๋“ค์–ด, UDTF๋Š” ๋„ˆ๋ฌด ์ปค์„œ ๋ฉ”๋ชจ๋ฆฌ์— ๋งž์ง€ ์•Š๋Š” ํŒŒ์ผ์„ ์ฝ๊ณ  ์ฒ˜๋ฆฌํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

UDF ๋˜๋Š” UDTF์˜ ๋Œ€์šฉ๋Ÿ‰ ํŒŒ์ผ์„ ์ฒ˜๋ฆฌํ•˜๋ ค๋ฉด SnowflakeFile ํด๋ž˜์Šค ๋˜๋Š” InputStream ํด๋ž˜์Šค๋ฅผ ์‚ฌ์šฉํ•˜์‹ญ์‹œ์˜ค. ์ž์„ธํ•œ ๋‚ด์šฉ์€ UDF ๋ฐ ํ”„๋กœ์‹œ์ € ์ฒ˜๋ฆฌ๊ธฐ๋กœ ๋น„์ •ํ˜• ๋ฐ์ดํ„ฐ ์ฒ˜๋ฆฌํ•˜๊ธฐ ์„น์…˜์„ ์ฐธ์กฐํ•˜์‹ญ์‹œ์˜ค.