Using Spark Scala APIs

1. Create a SnappySession
   SnappySession extends the SparkSession so you can mutate data, get much higher performance, etc.

   scala> val snappy = new org.apache.spark.sql.SnappySession(spark.sparkContext)
   // Import Snappy extensions
   scala> import snappy.implicits._
   

2. Create a dataset using the Spark APIs
   

   scala> val ds = Seq((1,"a"), (2, "b"), (3, "c")).toDS()
   

3. Define a schema for the table
   

   scala>  import org.apache.spark.sql.types._
   scala>  val tableSchema = StructType(Array(StructField("CustKey", IntegerType, false),
             StructField("CustName", StringType, false)))
   

4. Create a "column" table with a simple schema [String, Int] and default options
   For detailed option refer to the Row and Column Tables section.

   // Column tables manage data is columnar form and offer superior performance for analytic class queries.
   scala>  snappy.createTable(tableName = "colTable",
             provider = "column", // Create a SnappyData Column table
             schema = tableSchema,
             options = Map.empty[String, String], // Map for options
             allowExisting = false)
   

   SnappyData (SnappySession) extends SparkSession, so you can simply use all the Spark's APIs.

5. Insert the created DataSet to the column table "colTable"
   

   scala>  ds.write.insertInto("colTable")
   // Check the total row count.
   scala>  snappy.table("colTable").count
   

6. Create a row object using Spark's API and insert the row into the table
   Unlike Spark DataFrames SnappyData column tables are mutable. You can insert new rows to a column table.

   // Insert a new record
   scala>  import org.apache.spark.sql.Row
   scala>  snappy.insert("colTable", Row(10, "f"))
   // Check the total row count after inserting the row
   scala>  snappy.table("colTable").count
   

7. Create a "row" table with a simple schema [String, Int] and default options
   For detailed option refer to the Row and Column Tables section.

   // Row formatted tables are better when datasets constantly change or access is selective (like based on a key)
   scala>  snappy.createTable(tableName = "rowTable",
   provider = "row",
   schema = tableSchema,
   options = Map.empty[String, String],
   allowExisting = false)
   

8. Insert the created DataSet to the row table "rowTable"
   

   scala>  ds.write.insertInto("rowTable")
   // Check the row count
   scala>  snappy.table("rowTable").count
   

9. Insert a new record
   

   scala>  snappy.insert("rowTable", Row(4, "d"))
   //Check the row count now
   scala>  snappy.table("rowTable").count
   

10. Change some data in the row table
    

    // Updating a row for customer with custKey = 1
    scala>  snappy.update(tableName = "rowTable", filterExpr = "CUSTKEY=1",
                    newColumnValues = Row("d"), updateColumns = "CUSTNAME")
    
    scala>  snappy.table("rowTable").orderBy("CUSTKEY").show
    
    // Delete the row for customer with custKey = 1
    scala>  snappy.delete(tableName = "rowTable", filterExpr = "CUSTKEY=1")
    
    // Drop the existing tables
    scala>  snappy.dropTable("rowTable", ifExists = true)
    scala>  snappy.dropTable("colTable", ifExists = true)