-- Copyright 2025 Google LLC
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
--     http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.

CREATE OR REPLACE EXTERNAL TABLE
  `${PROJECT_ID}.${DATASET_ID}`.raw_sales ( location_id STRING,
    product_id STRING,
    sales_value INTEGER,
    sales_cost INTEGER)
WITH PARTITION COLUMNS (dt STRING)

WITH CONNECTION `${CONNECTION_NAME}`
    OPTIONS(hive_partition_uri_prefix = "gs://${DATA_BUCKET_NAME}/sales/",
            uris = ['gs://${DATA_BUCKET_NAME}/sales/dt=*'],
        max_staleness = INTERVAL 8 HOUR,
        metadata_cache_mode = 'AUTOMATIC',
        format = 'CSV')