Notebooks and SQL ================= This chapter shows how you work with the data: in a Python and Spark notebook in JupyterHub, and with SQL. The examples follow the data product from :doc:`data-engineer`: the catalog ``finance`` and its table ``finance.facturen.regels``. Signing in to JupyterHub ------------------------ 1. Open ``https://.dataprocessing.datafoundation.eu`` and choose **Sign in with OAuth 2.0**. .. image:: /_static/img/user-manual/notebooks/01-inloggen.png :alt: The JupyterHub sign-in page with the button Sign in with OAuth 2.0 :width: 700px 2. Sign in with your platform account (see :doc:`introduction`). JupyterHub starts your own notebook server. The first time this takes a few minutes: the page says **Your server is starting up** and opens JupyterLab by itself when it is ready. .. image:: /_static/img/user-manual/notebooks/02-server-start.png :alt: The JupyterHub page that says Your server is starting up :width: 700px 3. JupyterLab opens with the file browser on the left and the launcher on the right. Choose **Python 3 (ipykernel)** under **Notebook** to start a new notebook. .. image:: /_static/img/user-manual/notebooks/03-jupyterlab.png :alt: JupyterLab with the launcher and the file browser :width: 700px .. note:: Your notebook server stops by itself after an hour without activity. Your notebooks stay in your own home folder and are there when the server starts again. The number of servers that run at the same time is limited: if JupyterHub says it is at capacity, try again in a few minutes. To stop your server yourself, choose **File** > **Hub Control Panel** > **Stop My Server**. Your Spark session ------------------ In every notebook a Spark session called ``spark`` is ready; it starts the first time you use it. The session already knows the catalogs you may use, so you refer to data with the three-part name ``catalog.schema.table``. Without a catalog, Spark uses ``main``. .. note:: Your notebook has no direct access to the files of the data lake. Spark reads and writes them through the platform, which checks for every file whether your team may use it. You never have to open the lake yourself. Creating a schema and a table ----------------------------- If you own a catalog, or you hold the role *writer* on it, you can create schemas and tables in it with Spark. Create the schema first, then the table. Always write tables in the **Delta** format: .. code-block:: python spark.sql("CREATE SCHEMA IF NOT EXISTS finance.facturen") rijen = [ (1, "Gemeente Noordwijk", "noord", 1250.00, "inkoop.noord@demo.invalid"), (2, "Waterschap De Delta", "zuid", 980.50, "inkoop.zuid@demo.invalid"), ] df = spark.createDataFrame(rijen, ["factuur_id", "klant", "regio", "bedrag", "email"]) df.write.format("delta").saveAsTable("finance.facturen.regels") spark.table("finance.facturen.regels").show() Run a cell with **Shift+Enter**, or run the whole notebook with **Run** > **Run All Cells**. .. image:: /_static/img/user-manual/notebooks/04-tabel-aanmaken.png :alt: A notebook that creates the schema finance.facturen and the table regels and shows its rows :width: 700px The table now appears in the catalog tree of Security Center, owned by your team (refresh the page if you do not see it yet). For ingestion with the metadata-driven framework, ``Platform().write_to_table`` and ``upsert_table`` write to the same tables, see :doc:`../mdif/index`. .. warning:: Without ``.format("delta")`` Spark tries to create a Parquet table and the platform refuses it with the message "Unity Catalog does not support non-Delta managed table". See :doc:`troubleshooting`. .. note:: A catalog or schema that someone creates while your notebook is running is not known to your session yet. Restart the kernel (**Kernel** > **Restart Kernel**) to see it. Reading data you have filtered access to ---------------------------------------- You read a table that your team only holds a filtered role on exactly like any other table. The platform applies the row filter and the column mask for you. In this example the team Marketing holds the role ``regio_noord``: only the rows of the region *noord*, with the column ``email`` masked. .. code-block:: python spark.table("finance.facturen.regels").show(truncate=False) spark.sql(""" SELECT regio, count(*) AS facturen, round(sum(bedrag), 2) AS totaal FROM finance.facturen.regels GROUP BY regio """).show() .. image:: /_static/img/user-manual/notebooks/05-gefilterd.png :alt: A notebook that reads the table as a member of Team Marketing: two rows of the region noord, the email column empty :width: 700px Totals and counts only cover the rows you may see. You cannot write to a table you hold a filtered role on. SQL with Trino and BI tools --------------------------- BI tools and other SQL clients connect to the Trino address of your organization with your platform account. They see the same catalogs and the same access rules as you. In a notebook, Trino is also available as read-only Spark catalogs whose names start with ``trino``. The connection settings are in Security Center: choose the tile **Trino** on the dashboard. The dialog **Connect to Trino** shows the host, the port, the default catalog, a JDBC URL and a Python example, each with a copy button. To query from a SQL client on your own computer, use one that has a Trino driver, for example DBeaver: 1. Install `DBeaver Community `_. 2. Choose **Database** > **New Database Connection** and pick **Trino**. 3. Choose **URL** and paste the JDBC URL from the dialog **Connect to Trino**. Fill in your platform user name and leave the password empty. DBeaver downloads the driver the first time. 4. Choose **Test Connection** or **Finish**. A browser opens: sign in with your platform account. After that the catalogs appear in the navigator, with the same access as in Security Center. The Trino CLI works the same way: ``trino --server https://.trino.datafoundation.eu --external-authentication``. SQL Server Management Studio (SSMS) and Azure Data Studio cannot connect: they only talk to Microsoft SQL Server, and Trino is not a SQL Server. Installing a Trino ODBC driver does not change this: SSMS cannot use other drivers. The Trino web page (``https://.trino.datafoundation.eu``) shows the queries that run, not the catalogs and tables; use a SQL client to browse those. For Power BI, see your administrator. Scheduled notebooks with Airflow -------------------------------- To run a notebook on a schedule, use Apache Airflow with the ``SparkNotebookOperator``, see :doc:`../airflow/index`. A scheduled notebook runs as its own service identity, which needs access to the data like any team does. Airflow is an optional product; ask your administrator for its address and whether it runs for your organization. 1. Open Airflow and choose **Sign In with keycloak**. You sign in with the same platform account as everywhere else. .. image:: /_static/img/user-manual/notebooks/06-airflow-aanmelden.png :alt: The Airflow sign-in page with the button Sign In with keycloak :width: 700px 2. **Dags** lists the workflows that Airflow knows. A new Dag starts paused: switch it on with the toggle at the start of the row. The list shows **Active** Dags by default; choose **All** to see the paused ones too. A red number next to the number of Dags means that some files could not be loaded; open it to read why. .. image:: /_static/img/user-manual/notebooks/07-airflow-dags.png :alt: The Airflow Dags page listing fourteen Dags in a table :width: 700px