Legacy SQL의 맞춤 설정 함수

이 문서에서는 Legacy SQL 쿼리 문법에서 JavaScript 맞춤 설정 함수를 사용하는 방법을 자세히 설명합니다. BigQuery의 기본 쿼리 문법은 GoogleSQL입니다. GoogleSQL의 사용자 정의 함수에 대한 자세한 내용은 GoogleSQL 사용자 정의 함수를 참조하세요.

BigQuery legacy SQL에서는 JavaScript로 작성된 사용자 정의 함수(UDF)를 지원합니다. UDF는 MapReduce의 'Map' 함수와 비슷하게, 단일 행을 입력으로 사용하고 0개 이상의 행을 출력으로 생성합니다. 출력은 입력과 다른 스키마를 포함할 수 있습니다.

GoogleSQL의 사용자 정의 함수에 대한 자세한 내용은 GoogleSQL의 사용자 정의 함수를 참조하세요.

UDF 예시

// UDF definition
function urlDecode(row, emit) {
  emit({title: decodeHelper(row.title),
        requests: row.num_requests});
}

// Helper function with error handling
function decodeHelper(s) {
  try {
    return decodeURI(s);
  } catch (ex) {
    return s;
  }
}

// UDF registration
bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  urlDecode  // The function reference
);

맨 위로

UDF 구조

function name(row, emit) {
  emit(<output data>);
}

BigQuery UDF는 테이블 또는 하위 SELECT 쿼리 결과의 개별 행에서 작동합니다. UDF에는 두 가지 공식 매개변수가 있습니다.

row: 입력 행입니다.
emit: 출력 데이터를 수집하도록 BigQuery에서 사용되는 후크입니다. emit 함수는 출력 데이터의 단일 행을 나타내는 JavaScript 객체인 매개변수 한 개만 사용합니다. emit 함수는 여러 행의 데이터가 출력되도록 루프와 같은 곳에서 여러 번 호출될 수 있습니다.

다음 코드 예시는 기본 UDF를 보여줍니다.

function urlDecode(row, emit) {
  emit({title: decodeURI(row.title),
        requests: row.num_requests});
}

UDF 등록

BigQuery SQL에서 함수가 호출될 수 있게 하려면 함수 이름을 등록해야 합니다. 등록 이름은 JavaScript에서 함수에 사용된 이름과 일치할 필요가 없습니다.

bigquery.defineFunction(
  '<UDF name>',  // Name used to call the function from SQL

  ['<col1>', '<col2>'],  // Input column names

  // JSON representation of the output schema
  [<output schema>],

  // UDF definition or reference
  <UDF definition or reference>
);

입력 열

입력 열 이름은 입력 테이블 또는 서브 쿼리에 있는 열의 이름 또는 별칭(해당하는 경우)과 일치해야 합니다.

입력 열이 레코드인 경우, 레코드에서 액세스하려는 리프 필드를 입력 열 목록에서 지정해야 합니다.

예를 들어 특정 사용자의 이름과 나이가 저장된 레코드가 있는 경우:

person RECORD REPEATED
  name STRING OPTIONAL
  age INTEGER OPTIONAL

이름 및 나이에 대한 입력 지정자는 다음과 같습니다.

['person.name', 'person.age']

이름 또는 나이 없이 ['person']을 사용하면 오류가 발생합니다.

결과 출력은 스키마와 일치합니다. 여기에는 JavaScript 객체 배열이 표시되고, 각 객체에는 'name' 및 'age' 속성이 있습니다. 예를 들면 다음과 같습니다.

[ {name: 'alice', age: 23}, {name: 'bob', age: 64}, ... ]

출력 스키마

UDF로 생성되며, JSON으로 표현된 레코드의 스키마 또는 구조를 BigQuery에 제공해야 합니다. 스키마는 중첩 레코드를 포함하여 모든 지원되는 BigQuery 데이터 유형을 포함할 수 있습니다. 지원되는 유형 지정자는 다음과 같습니다.

부울
float
정수
레코드
문자열
타임스탬프

다음 코드 예시에서는 출력 스키마에 있는 레코드의 문법을 보여줍니다. 각 출력 필드에는 name 속성과 type 속성이 필요합니다. 중첩 필드는 fields 속성도 포함해야 합니다.

[{name: 'foo_bar', type: 'record', fields:
  [{name: 'a', type: 'string'},
   {name: 'b', type: 'integer'},
   {name: 'c', type: 'boolean'}]
}]

각 필드는 다음 값을 지원하는 선택적인 mode 속성을 포함할 수 있습니다.

null 사용 가능: 기본값이며 생략 가능합니다.
필수: 지정된 경우 해당 필드를 특정 값으로 설정해야 하며, 정의되지 않은 상태일 수 없습니다.
반복: 지정된 경우 해당 필드가 배열이어야 합니다.

emit() 함수에 전달되는 행은 출력 스키마의 데이터 유형과 일치해야 합니다. 출력 스키마에 제공되었지만 emit 함수에서 생략된 필드는 null로 출력됩니다.

UDF 정의 또는 참조

원하는 경우 bigquery.defineFunction에서 UDF를 인라인으로 정의할 수 있습니다. 예를 들면 다음과 같습니다.

bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  // The UDF
  function(row, emit) {
    emit({title: decodeURI(row.title),
          requests: row.num_requests});
  }
);

그렇지 않으면 UDF를 개별적으로 정의하고 bigquery.defineFunction에서 함수에 대한 참조를 전달할 수 있습니다. 예를 들면 다음과 같습니다.

// The UDF
function urlDecode(row, emit) {
  emit({title: decodeURI(row.title),
        requests: row.num_requests});
}

// UDF registration
bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  urlDecode  // The function reference
);

오류 처리

UDF 처리 중 예외 또는 오류가 발생하면 전체 쿼리가 실패합니다. 오류는 try-catch 블록을 사용하여 처리할 수 있습니다. 예를 들면 다음과 같습니다.

// The UDF
function urlDecode(row, emit) {
  emit({title: decodeHelper(row.title),
        requests: row.num_requests});
}

// Helper function with error handling
function decodeHelper(s) {
  try {
    return decodeURI(s);
  } catch (ex) {
    return s;
  }
}

// UDF registration
bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  urlDecode  // The function reference
);

UDF를 사용하여 쿼리 실행

You can use UDFs in legacy SQL with the bq 명령줄 도구 또는 BigQuery API를 사용하여 legacy SQL에서 UDF를 사용할 수 있습니다. Google Cloud 콘솔은 legacy SQL에서 UDF를 지원하지 않습니다.

bq 명령줄 도구 사용

하나 이상의 UDF가 포함된 쿼리를 실행하려면 Google Cloud CLI의 bq 명령줄 도구에서 --udf_resource 플래그를 지정합니다. 플래그 값은 Cloud Storage(gs://...) URI 또는 로컬 파일의 경로일 수 있습니다. 여러 UDF 리소스 파일을 지정하려면 이 플래그를 반복합니다.

UDF가 포함된 쿼리를 실행하려면 다음 문법을 사용합니다.

bq query --udf_resource=<file_path_or_URI> <sql_query>

다음 예시에서는 로컬 파일에 저장된 UDF를 사용하는 쿼리를 실행하고 마찬가지로 로컬 파일에 저장된 SQL 쿼리를 실행합니다.

UDF 만들기

UDF를 Cloud Storage에 또는 로컬 텍스트 파일로 저장할 수 있습니다. 예를 들어 다음 urlDecode UDF를 저장하려면 urldecode.js라는 파일을 만들고 다음 JavaScript 코드를 파일에 붙여넣은 후 파일을 저장합니다.

// UDF definition
function urlDecode(row, emit) {
  emit({title: decodeHelper(row.title),
        requests: row.num_requests});
}

// Helper function with error handling
function decodeHelper(s) {
  try {
    return decodeURI(s);
  } catch (ex) {
    return s;
  }
}

// UDF registration
bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  urlDecode  // The function reference
);

쿼리 만들기

또한 명령줄이 너무 길어지지 않도록 파일에 쿼리를 저장할 수도 있습니다. 예를 들어 이름이 query.sql인 로컬 파일을 만들고 다음 BigQuery 문을 파일에 붙여넣을 수 있습니다.

#legacySQL
SELECT requests, title
FROM
  urlDecode(
    SELECT
      title, sum(requests) AS num_requests
    FROM
      [fh-bigquery:wikipedia.pagecounts_201504]
    WHERE language = 'fr'
    GROUP EACH BY title
  )
WHERE title LIKE '%ç%'
ORDER BY requests DESC
LIMIT 100

파일을 저장한 후에는 명령줄에서 파일을 참조할 수 있습니다.

쿼리 실행

UDF와 쿼리를 개별 파일로 정의한 후에는 명령줄에서 이를 참조할 수 있습니다. 예를 들어 다음 명령어는 사용자가 이름이 query.sql인 파일로 저장한 쿼리를 실행하고 사용자가 만든 UDF를 참조합니다.

$ bq query --udf_resource=urldecode.js "$(cat query.sql)"

BigQuery API 사용

configuration.query

UDF를 사용하는 쿼리에는 쿼리에 사용할 수 있도록 코드 또는 코드 리소스에 대한 위치를 제공하는 userDefinedFunctionResources 요소가 포함되어야 합니다. 제공된 코드에는 쿼리에서 참조되는 모든 UDF의 등록 함수 호출이 포함되어야 합니다.

코드 리소스

쿼리 구성에는 Cloud Storage에 저장된 JavaScript 소스 파일에 대한 참조 외에도 JavaScript 코드 blob이 포함될 수 있습니다.

인라인 JavaScript 코드 blob은 userDefinedFunctionResource 요소의 inlineCode 섹션에 입력되어 있습니다. 하지만 여러 쿼리 간에 재사용 또는 참조되는 코드는 Cloud Storage에 저장하고 외부 리소스로 참조해야 합니다.

Cloud Storage에서 JavaScript 소스 파일을 참조하려면 userDefinedFunctionResource 요소의 resourceURI 섹션을 파일의 gs:// URI로 설정합니다.

쿼리 구성에는 userDefinedFunctionResource 요소가 여러 개 포함될 수 있습니다. 각 요소는 inlineCode 섹션 또는 resourceUri 섹션을 포함할 수 있습니다.

예

다음 JSON 예시는 UDF 리소스 두 개를 참조하는 쿼리 요청을 보여줍니다. 이러한 두 리소스는 인라인 코드의 blob 한 개와 Cloud Storage에서 읽을 lib.js 파일 한 개입니다. 이 예시에서 myFunc 및 myFunc의 등록 호출은 lib.js에서 제공됩니다.

{
  "configuration": {
    "query": {
      "userDefinedFunctionResources": [
        {
          "inlineCode": "var someCode = 'here';"
        },
        {
          "resourceUri": "gs://some-bucket/js/lib.js"
        }
      ],
      "query": "select a from myFunc(T);"
    }
  }
}

맨 위로

권장사항

UDF 개발

UDF 테스트 도구를 사용하면 BigQuery 비용 부과 없이 UDF를 테스트하고 디버깅할 수 있습니다.

입력 사전 필터링

입력을 UDF로 전달하기 전에 쉽게 필터링할 수 있으면 쿼리 속도가 빨라지고 비용이 낮아집니다.

쿼리 실행 예시에서는 전체 테이블 대신 urlDecode에 대한 입력으로 서브 쿼리가 전달됩니다. [fh-bigquery:wikipedia.pagecounts_201504] 테이블에는 약 56억 개 행이 있습니다. 전체 테이블에서 UDF를 실행하면 JavaScript 프레임워크가 필터링된 서브 쿼리를 할 때보다 21배나 많은 행을 처리해야 합니다.

영구적인 변경 가능 상태 금지

UDF 호출에서는 변경 가능 상태를 저장하거나 액세스하지 않는 것이 좋습니다. 다음 코드 예시에서는 이러한 시나리오를 보여줍니다.

// myCode.js
var numRows = 0;

function dontDoThis(r, emit) {
  emit({rowCount: ++numRows});
}

// The query.
SELECT max(rowCount) FROM dontDoThis(t);

BigQuery가 쿼리를 여러 노드 간에서 샤딩하므로 위 예시는 예상한 대로 작동하지 않습니다. 각 노드에는 numRows의 개별 값을 누적하는 독립 실행형 JavaScript 처리 환경이 포함되어 있습니다.

효율적인 메모리 사용

JavaScript 처리 환경은 쿼리당 사용 가능한 메모리가 제한되어 있습니다. 로컬 상태를 너무 많이 누적하는 UDF 쿼리는 메모리 소진으로 인해 실패할 수 있습니다.

SELECT 쿼리 확장

UDF에서 선택되는 열을 명시적으로 나열해야 합니다. SELECT * FROM <UDF name>(...)은 지원되지 않습니다.

입력 행 데이터의 구조를 살펴보려면 JSON.stringify()를 사용하여 문자열 출력 열을 내보내면 됩니다.

bigquery.defineFunction(
  'examineInputFormat',
  ['some', 'input', 'columns'],
  [{name: 'input', type: 'string'}],
  function(r, emit) {
    emit({input: JSON.stringify(r)});
  }
);

맨 위로

한도

단일 행을 처리할 때 UDF가 출력하는 데이터 양은 약 5MB 이하여야 합니다.
각 사용자가 특정 프로젝트에서 동시에 실행할 수 있는 UDF 쿼리 수는 약 6개로 제한됩니다. 동시 쿼리 제한을 초과했다는 오류가 발생하면 몇 분 정도 기다린 후 다시 시도하세요.
UDF가 시간 초과되어 쿼리를 완료하지 못할 수 있습니다. 시간 제한은 5분 정도로 짧을 수 있지만, 해당 함수가 소비하는 사용자 CPU 시간과 JS 함수에 대한 입력 및 출력 크기를 포함한 여러 요소에 따라 달라질 수 있습니다.
쿼리 작업에 포함될 수 있는 UDF 리소스 수는 최대 50개입니다(인라인 코드 blob 또는 외부 파일).
각 인라인 코드 BLOB 크기는 최대 32KB로 제한됩니다. 더 큰 코드 리소스를 사용하려면 코드를 Cloud Storage에 저장하고 외부 리소스로 참조하세요.
각 외부 코드 리소스 크기는 최대 1MB로 제한됩니다.
모든 외부 코드 리소스의 누적 크기는 최대 5MB로 제한됩니다.

맨 위로

제한사항

DOM 객체 Window, Document, Node와 이를 필요로 하는 함수는 지원되지 않습니다.
네이티브 코드를 사용하는 JavaScript 함수는 지원되지 않습니다.
JavaScript의 비트 연산은 32비트만 처리합니다.
비확정성으로 인해 맞춤 설정 함수를 호출하는 쿼리는 캐시 처리된 결과를 사용할 수 없습니다.

맨 위로