sqlglobal-variablesfoundry-code-workbooks

How can I declare current_date as variable in global code (Code Workbook)


I try to declare a current_date() variable in the global code.

enter image description here

After this I try to reference this column with SQL: where column = aktueller_tag


Solution

  • Guten tag mi_mlr, I see that you are a new contributor. Can you please frame the question as a minimum verifiable working example? I'll do my best to answer given the information you provided:

    Once you get the global variable using the current_date(), you can reference this within any node/dataset in Code Workbooks.

    This is what is in my global code:

    from pyspark.sql import functions as F
    aktueller_tag = F.current_date()
    

    Then from the console or a dataset node, I can run:

    >>> print(aktueller_tag)
    Column<b'current_date()'>
    

    Since this is a column, I can add a new column to an existing dataset.

    df = df.withColumn("today", aktueller_tag)
    

    Here is an end to end example with the notional flight data in Foundry:

    enter image description here

    This is an example in the console:

    >>> flight.withColumn("today",aktueller_tag)
    DataFrame[unique_flight_id: int, flight_id: string, aircraft_registration: string, departure_date: date, arrival_date: date, domestic_or_international: string, scheduled_departure_airport: string, scheduled_arrival_airport: string, today: date]
    
    
    >>> flight.withColumn("today",aktueller_tag).select("today").show(1)
    +----------+
    |     today|
    +----------+
    |2022-03-30|
    +----------+
    

    Now if you wanted to write the filter, an easy way would be:

    flight = flight.withColumn("today",aktueller_tag)
    flight_today = flight.where(F.col("today")==F.col("departure_date"))
    

    Where departure_date is the column you wanted to filter on. I hope this helps!