<jupyter_start><jupyter_text>![](../../jpg/stock_small.jpg)This is the first notebook of a series to show how to use Snowpark on Snowflake. This notebook provides a quick-start guide and an introduction to the Snowpark DataFrame API. The notebook explains the steps for setting up the environment (REPL), and how to resolve dependencies to Snowpark. After a simple "Hello World" example you will learn about the Snowflake DataFrame API, projections, filters, and joins.In this notebook we will learn how to - [Quick Start](Quick-Start): Set up the environment- [Hello World](Hello-World): First steps - [Snowflake DataFrame API](Snowflake-Dataframes): Query the Snowflake Sample Datasets via Snowflake DataFrames- [Conclusion](Conclusion): Conclusion and What's next  Prerequisite To get started you need a Snowflake account and read/write access to a database. If you do not have a Snowflake account, you can sign up for a [free trial](https://signup.snowflake.com/). It doesn't even require a credit card.  Quick Start First, we have to set up the environment for our notebook. The instructions for setting up the environment are in the Snowpark documentation in section [configuring-the-jupyter-notebook-for-snowpark](https://docs.snowflake.com/en/developer-guide/snowpark/quickstart-jupyter.htmlconfiguring-the-jupyter-notebook-for-snowpark).  Step 1Configure the notebook to use a Maven repository for a library that Snowpark depends on.<jupyter_code>import sys.process._
val osgeoRepo = coursierapi.MavenRepository.of("https://repo.osgeo.org/repository/release")
interp.repositories() ++= Seq(osgeoRepo)<jupyter_output><empty_output><jupyter_text>Step 2Create a directory (if it doesn't exist) for temporary files created by the [REPL](https://ammonite.io/Ammonite-REPL) environment. To avoid any side effects from previous runs, we also delete any files in that directory.**Note: Make sure that you have the operating system permissions to create a directory in that location.****Note: If you are using multiple notebooks, you’ll need to create and configure a separate REPL class directory for each notebook.**<jupyter_code>import ammonite.ops._
import ammonite.ops.ImplicitWd._

// This folder is used to store generated REPL classes, which will later be used in UDFs.
// Please provide an empty folder path. This is essential for Snowpark UDFs to work
val replClassPath = pwd+"/repl_classes"

// Delete any old files in the directory.
import sys.process._
s"rm -rf $replClassPath" !

// Create the REPL class folder.
import sys.process._
s"mkdir -p $replClassPath" !<jupyter_output><empty_output><jupyter_text>Step 3Configure the compiler for the Scala REPL. This does the following:- Configures the compiler to generate classes for the REPL in the directory that you created earlier.- Configures the compiler to wrap code entered in the REPL in classes, rather than in objects.- Adds the directory that you created earlier as a dependency of the REPL interpreter.<jupyter_code>// Generate all repl classes in REPL class folder
interp.configureCompiler(_.settings.outputDirs.setSingleOutput(replClassPath))
interp.configureCompiler(_.settings.Yreplclassbased.value = true)
interp.load.cp(os.Path(replClassPath))<jupyter_output><empty_output><jupyter_text>Step 4Import the Snowpark library from Maven.<jupyter_code>import $ivy.`com.snowflake:snowpark:0.10.0`<jupyter_output><empty_output><jupyter_text>To create a session, we need to authenticate ourselves to the Snowflake instance. Though it might be tempting to just override the authentication variables below with hard coded values, it's not considered best practice to do so. If you  share your version of the notebook, you might disclose your credentials by mistake to the recipient. Even worse, if you upload your notebook to a public code repository, you might advertise your credentials to the whole world. To prevent that, you should keep your credentials in an external file (like we are doing here).Then, update your credentials in that file and they will be saved on your local machine. Even better would be to switch from user/password authentication to [private key authentication](https://docs.snowflake.com/en/user-guide/key-pair-auth.html). Copy the credentials template file creds/template_credentials.txt to creds/credentials.txt and update the file with your credentials. Put your key files into the same directory or update the location in your credentials file.<jupyter_code>import com.snowflake.snowpark._
import com.snowflake.snowpark.functions._

val session = Session.builder.configFile("creds/credentials.txt").create<jupyter_output><empty_output><jupyter_text>Step 5Add the Ammonite kernel classes as dependencies for your UDF.<jupyter_code>def addClass(session: Session, className: String): String = {
  var cls1 = Class.forName(className)
  val resourceName = "/" + cls1.getName().replace(".", "/") + ".class"
  val url = cls1.getResource(resourceName)
  val path = url.getPath().split(":").last.split("!").head
  session.addDependency(path)
  path
}
addClass(session, "ammonite.repl.ReplBridge$")
addClass(session, "ammonite.interp.api.APIHolder")
addClass(session, "pprint.TPrintColors")<jupyter_output><empty_output><jupyter_text>Hello World Congratulations! You have successfully connected from a Jupyter Notebook to a Snowflake instance. Now we are ready to write our first "Hello World" program using Snowpark. That is as easy as the line in the cell below. After you have executed the cell below, you should see an output similar to    [scala-interpreter-1]  INFO (Logging.scala:22) - Actively querying parameter snowpark_lazy_analysis from server.    [scala-interpreter-1]  INFO (Logging.scala:22) - Execute query [queryID: 019e28e6-05025203-0000-22410336b00a]  SELECT  *  FROM (SELECT 'Hello World!' greeting) LIMIT 10    ----------------    |"GREETING"    |    ----------------    |Hello World!  |    ----------------<jupyter_code>session.sql("SELECT 'Hello World!' greeting").show()<jupyter_output><empty_output><jupyter_text>Note that Snowpark has automatically translated the Scala code into the familiar "Hello World!" SQL statement. This means that we can execute arbitrary SQL by using the **sql** method of the **session** class. However, this doesn't really show the power of the new Snowpark API. At this point it's time to review the [Snowpark API documentation](https://docs.snowflake.com/en/developer-guide/snowpark/reference/scala/com/snowflake/snowpark/index.html). It provides valuable information on how to use the Snowpark API. Let's now create a new *Hello World!* cell, that uses the Snowpark API, specifically the [DataFrame API](https://docs.snowflake.com/en/developer-guide/snowpark/reference/scala/com/snowflake/snowpark/index.html?search=dataframe). To use the DataFrame API we first create a row and a schema and then a DataFrame based on the row and the schema.<jupyter_code>import com.snowflake.snowpark.types._

val helloWorldDf=session.createDataFrame(Seq(("Hello World!"))).toDF("Greeting")

helloWorldDf
   .show<jupyter_output><empty_output><jupyter_text>Snowflake DataFrames After having mastered the *Hello World!* stage, we now can query Snowflake tables using the DataFrame API. To do so, we will query the [Snowflake Sample Database](https://docs.snowflake.com/en/user-guide/sample-data.html) included in any Snowflake instance. As you may know, the TPCH data sets come in different sizes from 1 TB to 1 PB (1000 TB). For starters we will query the orders table in the 10 TB dataset size. Instead of writing a SQL statement we will use the DataFrame API. The advantage is that DataFrames can be built as a pipeline. Let's take a look at the demoOrdersDf.    val demoOrdersDf=session.table(demoDataSchema :+ "ORDERS")    In contrast to the initial *Hello World!* example above, we now map a Snowflake table to a DataFrame. The definition of a DataFrame doesn't take any time. It's just defining metadata. To get the result, for instance the content of the Orders table, we need to [evaluate](https://docs.snowflake.com/en/developer-guide/snowpark/working-with-dataframes.htmlperforming-an-action-to-evaluate-a-dataframe) the DataFrame. One way of doing that is to apply the *[count()](https://docs.snowflake.com/en/developer-guide/snowpark/reference/scala/com/snowflake/snowpark/DataFrame.html?search=count)* action which returns the row count of the dataframe. In this case, the row count of the *Orders* table. Another method is the *[schema](https://docs.snowflake.com/en/developer-guide/snowpark/reference/scala/com/snowflake/snowpark/DataFrame.html?search=schema)* function.<jupyter_code>val size:String="10"
val demoDataSchema:Seq[String]=Seq("SNOWFLAKE_SAMPLE_DATA","TPCH_SF"+size)
val demoOrdersDf=session.table(demoDataSchema :+ "ORDERS")

demoOrdersDf
    .count()
demoOrdersDf
    .schema<jupyter_output><empty_output><jupyter_text>Next, we want to apply a projection. In SQL terms, this is the *select* clause. Instead of getting all of the columns in the Orders table, we are only interested in a few. This is accomplished by the *[select()](https://docs.snowflake.com/en/developer-guide/snowpark/reference/scala/com/snowflake/snowpark/DataFrame.html?search=select)* transformation. Note that we can just add additional qualifications to the already existing DataFrame of *demoOrdersDf* and create a new DataFrame that includes only a subset of columns. Lastly, instead of counting the rows in the DataFrame, this time we want to see the content of the DataFrame. To do so we need to evaluate the DataFrame. We can do that using another action *[show](https://docs.snowflake.com/en/developer-guide/snowpark/reference/scala/com/snowflake/snowpark/DataFrame.html?search=show)*.<jupyter_code>val demoOrdersProjectedDf=
        demoOrdersDf
            .select(col("O_ORDERKEY"), col("O_CUSTKEY"), col("O_ORDERSTATUS"), col("O_ORDERDATE"))

demoOrdersProjectedDf
    .show(20)<jupyter_output><empty_output><jupyter_text>Let's now assume that we do not want **all** the rows but only a subset of rows in a DataFrame. We can accomplish that with the *[filter()](https://docs.snowflake.com/en/developer-guide/snowpark/reference/scala/com/snowflake/snowpark/DataFrame.html?search=select)* transformation.<jupyter_code>val demoOrdersProjectedFilteredDf=
        demoOrdersProjectedDf
            .filter(col("O_ORDERSTATUS")===lit("O"))
                    
demoOrdersProjectedFilteredDf
    .sort(col("O_ORDERKEY"))
    .show(20)<jupyter_output><empty_output><jupyter_text>And lastly, we want to create a new DataFrame which joins the Orders table with the LineItem table. Again, we are using our previous DataFrame that is a projection and a filter against the Orders table. We can join that DataFrame to the *LineItem* table and create a new DataFrame. We then apply the *[select()](https://docs.snowflake.com/en/developer-guide/snowpark/reference/scala/com/snowflake/snowpark/DataFrame.html?search=filter)* transformation. Again, to see the result we need to evaluate the DataFrame, for instance by using the *show()* action.<jupyter_code>val demoLinesDf=session.table(demoDataSchema :+ "LINEITEM")
val demoOrdersLinesDf=
        demoOrdersProjectedFilteredDf
            .join(demoLinesDf,col("L_ORDERKEY")===col("O_ORDERKEY"))
            .select(col("O_ORDERKEY"),col("O_ORDERSTATUS"),col("L_LINENUMBER"),col("L_LINESTATUS"))

demoOrdersLinesDf
    .sort(col("O_ORDERKEY"),col("L_LINENUMBER"))
    .show(100)
val demoOrdersCountByDateAndStatus=
        demoOrdersProjectedDf
            .select(col("O_ORDERDATE"),col("O_ORDERSTATUS"), col("O_ORDERKEY"))
            .groupBy(col("O_ORDERDATE"),col("O_ORDERSTATUS"))
            .agg(count(col("O_ORDERKEY")).name("O_COUNT"))

val demoOrdersCountByDateAndStatusArr=demoOrdersCountByDateAndStatus
    .sort(col("O_ORDERDATE"),col("O_ORDERSTATUS"))
    .collect()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09563462023719405, 'fraction_numerical': 0.005467238623938094, 'mean_word_length': 5.138358286009293, 'pattern_counts': {'":': 1, '<': 49, '<?xml version=': 0, '>': 49, 'https://': 15, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '586847', 'license': 'Apache-2.0', 'n_tokens_mistral': 3559, 'n_tokens_neox': 3431, 'n_words': 1210, 'path': 'notebook/part1/part1.ipynb', 'repo_name': 'Snowflake-Labs/sfguide_snowpark_on_jupyter', 'size': '11889'}