googlecloudplatform / analytics-componentized-patterns Goto Github PK

%%bigquery --params $LTV_PARAMS --project $PROJECT_ID

DECLARE MAX_STDV_MONETARY INT64 DEFAULT @MAX_STDV_MONETARY;
DECLARE MAX_STDV_QTY INT64 DEFAULT @MAX_STDV_QTY;

CREATE OR REPLACE TABLE `ltv_ecommerce.20_aggred` AS
SELECT
  customer_id,
  order_day,
  ROUND(day_value_after_returns, 2) AS value,
  day_qty_after_returns as qty_articles,
  day_num_returns AS num_returns,
  CEIL(avg_time_to_return) AS time_to_return
FROM (

.....

Traceback (most recent call last):

  File "/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/magics/magics.py", line 515, in _cell_magic
    params_option_value, rest_of_args = _split_args_line(line)

  File "/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/magics/magics.py", line 720, in _split_args_line
    tree = scanner.input_line()

  File "/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/magics/line_arg_parser/parser.py", line 203, in input_line
    options = self.option_list()

  File "/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/magics/line_arg_parser/parser.py", line 264, in option_list
    option = self.params_option()

  File "/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/magics/line_arg_parser/parser.py", line 319, in params_option
    opt_value = self.py_dict()

  File "/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/magics/line_arg_parser/parser.py", line 333, in py_dict
    dict_items = self.dict_items()

  File "/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/magics/line_arg_parser/parser.py", line 347, in dict_items
    item = self.dict_item()

  File "/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/magics/line_arg_parser/parser.py", line 371, in dict_item
    value = self.py_value()

  File "/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/magics/line_arg_parser/parser.py", line 423, in py_value
    self.error(msg, exc_type=QueryParamsParseError)

  File "/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/magics/line_arg_parser/parser.py", line 193, in error
    raise exc_type(message)

QueryParamsParseError: Unexpected token type UNKNOWN at position 27.


The above exception was the direct cause of the following exception:

Traceback (most recent call last):

  File "/opt/conda/lib/python3.7/site-packages/IPython/core/interactiveshell.py", line 3457, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)

  File "/tmp/ipykernel_17444/3535280929.py", line 1, in <module>
    get_ipython().run_cell_magic('bigquery', '--params $LTV_PARAMS --project $PROJECT_ID', '\nDECLARE MAX_STDV_MONETARY INT64 DEFAULT @MAX_STDV_MONETARY;\nDECLARE MAX_STDV_QTY INT64 DEFAULT @MAX_STDV_QTY;\n\nCREATE OR REPLACE TABLE `ltv_ecommerce.20_aggred` AS\nSELECT\n  customer_id,\n  order_day,\n  ROUND(day_value_after_returns, 2) AS value,\n  day_qty_after_returns as qty_articles,\n  day_num_returns AS num_returns,\n  CEIL(avg_time_to_return) AS time_to_return\nFROM (\n  SELECT\n    customer_id,\n    order_day,\n    SUM(order_value_after_returns) AS day_value_after_returns,\n    STDDEV(SUM(order_value_after_returns)) OVER(PARTITION BY customer_id ORDER BY SUM(order_value_after_returns)) AS stdv_value,\n    SUM(order_qty_after_returns) AS day_qty_after_returns,\n    STDDEV(SUM(order_qty_after_returns)) OVER(PARTITION BY customer_id ORDER BY SUM(order_qty_after_returns)) AS stdv_qty,\n    CASE\n      WHEN MIN(order_min_qty) < 0 THEN count(1)\n      ELSE 0\n    END AS day_num_returns,\n    CASE\n      WHEN MIN(order_min_qty) < 0 THEN AVG(time_to_return)\n      ELSE NULL\n    END AS avg_time_to_return\n  FROM (\n    SELECT \n      customer_id,\n      order_id,\n      -- Gives the order date vs return(s) dates.\n      MIN(transaction_date) AS order_day,\n      MAX(transaction_date) AS return_final_day,\n      DATE_DIFF(MAX(transaction_date), MIN(transaction_date), DAY) AS time_to_return,\n      -- Aggregates all products in the order \n      -- and all products returned later.\n      SUM(qty * unit_price) AS order_value_after_returns,\n      SUM(qty) AS order_qty_after_returns,\n      -- If negative, order has qty return(s).\n      MIN(qty) order_min_qty\n    FROM \n      `ltv_ecommerce.10_orders`\n    GROUP BY\n      customer_id,\n      order_id)\n  GROUP BY\n    customer_id,\n    order_day)\nWHERE\n  -- [Optional] Remove dates with outliers per a customer.\n  (stdv_value < MAX_STDV_MONETARY\n    OR stdv_value IS NULL) AND\n  (stdv_qty < MAX_STDV_QTY\n    OR stdv_qty IS NULL);\n\n\nSELECT * FROM `ltv_ecommerce.20_aggred` LIMIT 5;\n')

  File "/opt/conda/lib/python3.7/site-packages/IPython/core/interactiveshell.py", line 2419, in run_cell_magic
    result = fn(*args, **kwargs)

  File "/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/magics/magics.py", line 521, in _cell_magic
    raise rebranded_error from exc

  File "<string>", line unknown
SyntaxError: --params is not a correctly formatted JSON string or a JSON serializable dictionary