[[["이해하기 쉬움","easyToUnderstand","thumb-up"],["문제가 해결됨","solvedMyProblem","thumb-up"],["기타","otherUp","thumb-up"]],[["이해하기 어려움","hardToUnderstand","thumb-down"],["잘못된 정보 또는 샘플 코드","incorrectInformationOrSampleCode","thumb-down"],["필요한 정보/샘플이 없음","missingTheInformationSamplesINeed","thumb-down"],["번역 문제","translationIssue","thumb-down"],["기타","otherDown","thumb-down"]],["최종 업데이트: 2025-04-21(UTC)"],[[["\u003cp\u003eDataflow SQL allows for parameterized queries using either named parameters (prefixed with \u003ccode\u003e@\u003c/code\u003e) or positional parameters (prefixed with \u003ccode\u003e?\u003c/code\u003e), but not a mix of both within the same query.\u003c/p\u003e\n"],["\u003cp\u003eParameterized queries can be defined and run via the Google Cloud console's Dataflow SQL UI, which is able to define parameters for most types, or through the Google Cloud CLI, which supports \u003ccode\u003eARRAY\u003c/code\u003e and \u003ccode\u003eSTRUCT\u003c/code\u003e parameter types that are unavailable in the UI.\u003c/p\u003e\n"],["\u003cp\u003eWhen defining parameters, the specified data type and value must align with the type of the corresponding database object in the query, and each parameter type has a unique way to be defined and used.\u003c/p\u003e\n"],["\u003cp\u003eThe Google Cloud CLI uses the \u003ccode\u003e--parameter\u003c/code\u003e flag in the \u003ccode\u003egcloud dataflow sql query\u003c/code\u003e command to set each query parameter, formatted as \u003ccode\u003ename:type:value\u003c/code\u003e for named parameters or \u003ccode\u003e:type:value\u003c/code\u003e for positional parameters, allowing for the inclusion of \u003ccode\u003eARRAY\u003c/code\u003e, \u003ccode\u003eSTRUCT\u003c/code\u003e, and \u003ccode\u003eSTRING\u003c/code\u003e typed parameters.\u003c/p\u003e\n"],["\u003cp\u003eTimestamps used in query parameters require the type \u003ccode\u003eSTRING\u003c/code\u003e and need to be in the canonical timestamp format, allowing for comparison against \u003ccode\u003eTIMESTAMP\u003c/code\u003e fields in the query, by invoking the \u003ccode\u003eTIMESTAMP\u003c/code\u003e function on the parameter in the query.\u003c/p\u003e\n"]]],[],null,["# Using parameterized Dataflow SQL queries\n\nThis page explains how to run parameterized [Dataflow SQL queries](/dataflow/docs/guides/sql/dataflow-sql-intro#writing-queries).\n\nDataflow SQL supports named and positional query parameters. Specify query\nparameters with the following syntax:\n\n- Named parameters with the `@` character followed by an [identifier](/dataflow/docs/reference/sql/lexical#identifiers), such as `@param_name`\n- Positional parameters with the `?` character\n\nYou can use either named or positional parameters in a query but not both.\n| **Note:** Query parameters are substitutes for arbitrary expressions. You cannot substitute query parameters for identifiers, column names, table names, or other parts of a query\n\nRunning parameterized queries\n-----------------------------\n\nTo run a parameterized query, define the\n[data type](/dataflow/docs/reference/sql/data-types) and value of the query\nparameters. The type and value of the query parameters must match the type\nof the database objects that the parameters represent.\n\nYou can define query parameters using the Google Cloud console or\nGoogle Cloud CLI: \n\n### Console\n\nTo define query parameters, use the Dataflow SQL UI:\n\n1. Go to the Dataflow SQL UI.\n\n [Go to the Dataflow SQL UI](https://console.cloud.google.com/bigquery?qe=df)\n2. Enter the Dataflow SQL query into the query editor.\n\n For example, the following query selects taxi rides that cost at least the\n specified price: \n\n SELECT *\n FROM pubsub.topic.`pubsub-public-data`.`taxirides-realtime`\n WHERE\n ride_status = \"dropoff\"\n AND meter_reading \u003e= @price_min\n\n3. Click **Create Cloud Dataflow job** to open a panel of job options.\n\n4. In the **SQL query parameters** section of the panel, fill in the\n **Name** , **Type** , and **Value** fields. The name of the parameter in the\n example is `price_min` and the type is `FLOAT64`.\n\n If the parameter is positional, omit the **Name** field.\n5. (Optional) If the query contains more parameters, click **Add parameter**.\n\nThe Google Cloud console does not support `ARRAY` or `STRUCT`\nparameters. To specify `ARRAY` or `STRUCT` parameters, use the\nGoogle Cloud CLI.\n\n### gcloud\n\nTo define query parameters, use the\n[`gcloud dataflow sql query`](/sdk/gcloud/reference/dataflow/sql/query)\ncommand and `--parameter` flags.\n\nYou can repeat the `--parameter` flag for each query parameter. Set each\n`--parameter` flag to\n\u003cvar translate=\"no\"\u003ename\u003c/var\u003e`:`\u003cvar translate=\"no\"\u003etype\u003c/var\u003e`:`\u003cvar translate=\"no\"\u003evalue\u003c/var\u003e.\n\nThe following command runs a parameterized query on a Pub/Sub\nstream of taxi rides: \n\n```bash\ngcloud dataflow sql query \\\n --job-name=job-name \\\n --region=region \\\n --bigquery-dataset=destination-dataset \\\n --bigquery-table=destination-table \\\n --parameter=status:STRING:dropoff \\\n --parameter=price_min:FLOAT64:5.5 \\\n'SELECT *\nFROM pubsub.topic.`pubsub-public-data`.`taxirides-realtime`\nWHERE\n ride_status = @status\n AND meter_reading \u003e= @price_min'\n```\n\nIf the parameter is positional, set `--parameter` flags to\n`:`\u003cvar translate=\"no\"\u003etype\u003c/var\u003e`:`\u003cvar translate=\"no\"\u003evalue\u003c/var\u003e.\n\nUsing arrays in parameterized queries\n-------------------------------------\n\nTo use an array type in a query parameter, set the type to `ARRAY\u003c`\u003cvar translate=\"no\"\u003edata-type\u003c/var\u003e`\u003e`.\nConstruct the value as a comma-separated list of elements enclosed in\nsquare brackets, such as `[\"pickup\", \"enroute\", \"dropoff\"]`. \n\n### Console\n\nThe Dataflow SQL UI does not support `ARRAY` parameters in Dataflow SQL\nqueries.\n\n### gcloud\n\nTo define an array parameter, set the `--parameter` flag of the\n[`gcloud dataflow sql query`](/sdk/gcloud/reference/dataflow/sql/query)\ncommand to \u003cvar translate=\"no\"\u003ename\u003c/var\u003e`:ARRAY\u003c`\u003cvar translate=\"no\"\u003edata-type\u003c/var\u003e`\u003e:`\u003cvar translate=\"no\"\u003evalue\u003c/var\u003e\n\nThe following command runs query with a named array parameter on a\nPub/Sub stream of taxi rides: \n\n```bash\ngcloud dataflow sql query \\\n --job-name=job-name\n --region=region\n --bigquery-dataset=destination-dataset \\\n --bigquery-table=destination-table \\\n --parameter='status:ARRAY\u003cSTRING\u003e:[\"pickup\", \"enroute\", \"dropoff\"]' \\\n'SELECT *\nFROM pubsub.topic.`pubsub-public-data`.`taxirides-realtime`\nWHERE\n ride_status IN UNNEST(@status)'\n```\n\nFor more information, see the Dataflow SQL reference for [`ARRAY` types](/dataflow/docs/reference/sql/data-types#array-type).\n\nUsing timestamps in parameterized queries\n-----------------------------------------\n\nTo use a timestamp in a query parameter, set the type to `STRING` but construct\nthe value in the [canonical timestamp format](/dataflow/docs/reference/sql/data-types#canonical-format_2). \n\n### Console\n\nTo define timestamps parameters, use the Dataflow SQL UI:\n\n1. Go to the Dataflow SQL UI.\n\n [Go to the Dataflow SQL UI](https://console.cloud.google.com/bigquery?qe=df)\n2. Enter the Dataflow SQL query into the query editor.\n\n For example, the following query selects taxi rides that occurred after the\n specified date: \n\n SELECT *\n FROM pubsub.topic.`pubsub-public-data`.`taxirides-realtime`\n WHERE\n event_timestamp \u003e= TIMESTAMP (@date_min)\n\n3. Click **Create Cloud Dataflow job** to open a panel of job options.\n\n4. In the **SQL query parameters** section, click **Add parameter**.\n\n5. In the **Type** field, select `STRING`.\n\n6. In the **Value** field, enter a timestamp.\n\n### gcloud\n\nTo define an timestamp parameter, set the `--parameter` flag of the\n[`gcloud dataflow sql query`](/sdk/gcloud/reference/dataflow/sql/query)\ncommand to \u003cvar translate=\"no\"\u003ename\u003c/var\u003e`:STRING:`\u003cvar translate=\"no\"\u003evalue\u003c/var\u003e\n\nThe following command runs query with a named timestamp parameter on a\nPub/Sub stream of taxi rides: \n\n```bash\ngcloud dataflow sql query \\\n --job-name=job-name \\\n --region=region \\\n --bigquery-dataset=destination-dataset \\\n --bigquery-table=destination-table \\\n --parameter='date_min:STRING:2020-01-01 00:00:00.000 UTC' \\\n'SELECT *\nFROM pubsub.topic.`pubsub-public-data`.`taxirides-realtime`\nWHERE\n event_timestamp \u003e= TIMESTAMP (@date_min)'\n```\n\nFor more information, see the Dataflow SQL reference for the [`TIMESTAMP` types](/dataflow/docs/reference/sql/data-types#timestamp-type).\n\nUsing structs in parameterized queries\n--------------------------------------\n\nTo use a struct in a query parameter, set the type to `STRUCT\u003c`\u003cvar translate=\"no\"\u003efield-name\u003c/var\u003e` `\u003cvar translate=\"no\"\u003edata-type\u003c/var\u003e`, ...\u003e`.\nConstruct the value as a comma-separated list of key-value pairs.\n\nFor example, `STRUCT\u003cdate_min TIMESTAMP, status STRING\u003e` defines a struct with a\nfield named `date_min` of type `TIMESTAMP` and a field named `status` of type\n`STRING`. \n\n### Console\n\nThe Dataflow SQL UI does not support `STRUCT` parameters in Dataflow SQL\nqueries.\n\n### gcloud\n\nTo define an array parameter, set the `--parameter` flag of the\n[`gcloud dataflow sql query`](/sdk/gcloud/reference/dataflow/sql/query)\ncommand to \u003cvar translate=\"no\"\u003ename\u003c/var\u003e`:STRUCT\u003c`\u003cvar translate=\"no\"\u003efield-name\u003c/var\u003e` `\u003cvar translate=\"no\"\u003edata-type\u003c/var\u003e`, ...\u003e:`\u003cvar translate=\"no\"\u003evalue\u003c/var\u003e\n\nThe following command runs query with a named struct parameter on a\nPub/Sub stream of taxi rides: \n\n```bash\ngcloud dataflow sql query \\\n --job-name=job-name \\\n --region=region \\\n --bigquery-dataset=destination-dataset \\\n --bigquery-table=destination-table \\\n --parameter='rides:STRUCT\u003criders_min INT64, status STRING\u003e:\n {\"riders_min\": 2, \"status\": \"dropoff\"}' \\\n'SELECT *\nFROM pubsub.topic.`pubsub-public-data`.`taxirides-realtime`\nWHERE\n passenger_count \u003e= @rides.riders_min\n AND ride_status = @rides.status'\n```\n\nFor more information, see the Dataflow SQL reference for the [`STRUCT` types](/dataflow/docs/reference/sql/data-types#struct-type)."]]
