how to create temporary table snowflake using dataframe in java/scala

Question

I have a dataframe, I want to create a temporary table from it. Basically I have a set of string, with that I am creating a dataframe and with that I want to create a temporary table in snowflake.

Is there any better way to create temp table with list, if not then how to create temp table using dataframe in snowflake.

I am using 1.8.0 version of snowflake and scala/java

Answer 1

you can create a temporary table

spark.sparkContext._jvm.net.snowflake.spark.snowflake.Utils.runQuery(sfOptions, "create temporary table my_temporary_table (col1 int, col2 text)")

For more details visit

https://community.snowflake.com/s/article/HOW-To-Run-SHOW-Statements-using-Snowflake-Spark-connector

Answer 2

There is not currently an api to specify the table name. There is an open issue in the repository for that. At this moment you can do something like this:

        var session = Session.builder().configfile("").getOrCreate()

        var stringData = java.util.Arrays.asList("John", "Doe", "Alice", "Bob");
        
        Row[] rows = stringData.stream().map( x -> Row.create(x)).toArray(Row[]::new);
        
        DataFrame df = session.createDataFrame(rows, StructType.create(new StructField("value", DataTypes.StringType)));
        // this will save the data in temptable. You cant specify the table name
        df = df.cacheResult();
        // in most cases you dont need the table name as you can just keep working with this
        // dataframe in other operations. You can run df.explain() just to see the name of the temp table