FurcyPin

WITH price_history_per_shop_per_article AS (
  SELECT
    shop_id, 
    article_id,
    ARRAY_AGG((SELECT AS STRUCT T.* EXCEPT(shop_id, article_id)) ORDER BY start_datetime) as price_history
  FROM `supermarket.sell_price_history` T
  GROUP BY shop_id, article_id
)
, promotion_history_per_shop_per_article AS (
  SELECT

FurcyPin

from bigquery_frame import BigQueryBuilder
from bigquery_frame.auth import get_bq_client
from bigquery_frame.transformations import analyze
from bigquery_frame import functions as f

bigquery = BigQueryBuilder(get_bq_client())


query = """
    SELECT 

FurcyPin

df = bigquery.sql(query)

df.show()
# +----+------------+---------------------+--------------------------------------------+
# | id |       name |               types |                                  evolution |
# +----+------------+---------------------+--------------------------------------------+
# |  1 |  Bulbasaur | ['Grass', 'Poison'] | {'can_evolve': True, 'evolves_from': None} |
# |  2 |    Ivysaur | ['Grass', 'Poison'] |    {'can_evolve': True, 'evolves_from': 1} |
# |  3 |   Venusaur | ['Grass', 'Poison'] |   {'can_evolve': False, 'evolves_from': 2} |
# |  4 | Charmander |            ['Fire'] | {'can_evolve': True, 'evolves_from': None} |

FurcyPin

bq = BigQueryBuilder(get_bq_client())
df = bq.sql('''SELECT 1 as id, STRUCT(1 as a, STRUCT(1 as c, 1 as d) as b) as s''')
df.printSchema()
# root
#  |-- id: INTEGER (NULLABLE)
#  |-- s: RECORD (NULLABLE)
#  |    |-- a: INTEGER (NULLABLE)
#  |    |-- b: RECORD (NULLABLE)
#  |    |    |-- c: INTEGER (NULLABLE)
#  |    |    |-- d: INTEGER (NULLABLE)

FurcyPin

WITH T AS (
    /* SOME TRANSFORMATION */
)
SELECT 
  -- Please keep these column names sorted alphabetically
  col1,
  col2, 
  col3,
  col4,
  ...

FurcyPin

from bigquery_frame import BigQueryBuilder
from bigquery_frame.auth import get_bq_client
from bigquery_frame import functions as f

bigquery = BigQueryBuilder(get_bq_client())


df = bigquery.sql("""
    SELECT 1 as id, "Bulbasaur" as name, ["Grass", "Poison"] as types, NULL as other_col
    UNION ALL

FurcyPin

from pyspark.sql import SparkSession
from pyspark.sql import functions as f

spark = SparkSession.builder.master("local[1]").getOrCreate()

df = spark.sql("""
    SELECT 1 as id, "Bulbasaur" as name, ARRAY("Grass", "Poison") as types, NULL as other_col
    UNION ALL
    SELECT 2 as id, "Ivysaur" as name, ARRAY("Grass", "Poison") as types, NULL as other_col
""")