net.snowflake.client.VersionIT Maven / Gradle / Ivy
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
package net.snowflake.client;
import net.snowflake.client.loader.Loader;
import net.snowflake.client.loader.LoaderFactory;
import net.snowflake.client.loader.LoaderProperty;
import net.snowflake.client.loader.StreamLoader;
import net.snowflake.client.loader.Operation;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import java.util.Random;
import org.junit.After;
import org.junit.AfterClass;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;
import static org.junit.Assert.assertEquals;
/**
*
* @author dengovatov
*/
public class VersionIT
{
// test DB
static String DB = "DB", DBC = "DBC";
// test schemas
static String SC = "SC", SCC = "SCC";
// test tables
static String T = "T", T2 = "T2", TC = "TC";
// Before creation
static Long check1, check2, check3, check4, check5, check6, check7, check8;
/**
* Timeline
*
* check1
*
* DB, SC, DB.SC.T created
* T populated with one row
*
* check 2
*
* T modified an extra 5000001 rows
*
* check 3
*
* T2 created as a clone of T prior to check 2
* validate 1 row in T2
*
* check 4
*
* T2 modified, insert content of T
* validate 5000002 rows
* T is dropped
*
* check 5
*
* TC is created as clone of T2 before checkpoint 4
* validate 1 row in TC
*
* check 6
*
* SCC created as clone of SC prior to checkpoint 4
* validate T and T2 present, 5000001 rows in T, 1 in T2
* validate TC not present
*
* check 7
*
* undrop DB.SC.T
* drop DB.SCC.T2
*
* check 8
*
* validate DB
*
* create DBC as clone of DB prior to checkpoint 8
* validate DBC.SC.T exists, 5000001 rows
* validate DBC.SC.T2 exists, 5000002 rows
* validate DBC.SC.TC exists, 1 row
* validate DBC.SCC.T exists, 5000001 rows
* validate DBC.SCC.TC does not exist
*/
static Connection testConnection;
static Connection putConnection;
public VersionIT()
{
}
@BeforeClass
public static void setUpClass() throws Throwable
{
String accountStr = "testaccount";
if (System.getenv("SF_ACCOUNT") != null)
{
accountStr = System.getenv("SF_ACCOUNT");
}
// create a new connection
String connectionUrl = System.getenv("SF_JDBC_CONNECT_STRING");
// use the default connection string if it is not set in environment
if (connectionUrl == null)
{
connectionUrl = "jdbc:snowflake://localhost:8080";
}
connectionUrl = connectionUrl + "/?account=" + accountStr + "&user=snowman&password=test&schema=testschema&db=testdb";
Properties properties = new Properties();
properties.put("internal", "true");
properties.put("ssl", "off");
testConnection = DriverManager.getConnection(connectionUrl, properties);
putConnection = DriverManager.getConnection(connectionUrl, properties);
check1 = getTime();
testConnection.createStatement().execute(
"DROP DATABASE IF EXISTS DB");
testConnection.createStatement().execute(
"DROP DATABASE IF EXISTS DBC");
testConnection.createStatement().execute(
"CREATE DATABASE DB");
testConnection.createStatement().execute(
"CREATE SCHEMA SC");
testConnection.createStatement().execute(
"USE SCHEMA SC");
putConnection.createStatement().execute(
"USE DATABASE DB");
putConnection.createStatement().execute(
"USE SCHEMA SC");
testConnection.createStatement().execute(
"CREATE TABLE DB.SC.T(S VARCHAR, T timestamp)");
testConnection.createStatement().execute(
"insert into DB.SC.T values('blah', 0::timestamp)");
check2 = getTime();
//Insert data into T
testConnection.createStatement().execute(
"CREATE OR REPLACE STAGE loadertest URL='file:///tmp/loadertest'");
Map prop = new HashMap<>();
prop.put(LoaderProperty.tableName, "T");
prop.put(LoaderProperty.schemaName, "SC");
prop.put(LoaderProperty.databaseName, "DB");
prop.put(LoaderProperty.remoteStage, "loadertest");
prop.put(LoaderProperty.operation, Operation.INSERT);
prop.put(LoaderProperty.columns, Arrays.asList(new String[]{"S", "T"}));
Loader sl = LoaderFactory.createLoader(prop, putConnection, testConnection);
sl.start();
// INSERT
Random rnd = new Random();
long ct = System.nanoTime();
for (int i = 0; i < 5000000; i++)
{
Object[] row = new Object[]
{
String.valueOf("{\"foo_" + i + "\":" + rnd + "}"), new Date()
};
sl.submitRow(row);
}
sl.finish();
System.out.println(String.valueOf((long)((System.nanoTime() - ct)/200000.0)) + " rows/sec");
testConnection.createStatement().execute("DROP STAGE loadertest");
}
@AfterClass
public static void tearDownClass() throws SQLException
{
testConnection.createStatement().execute(
"DROP DATABASE IF EXISTS DB");
testConnection.createStatement().execute(
"DROP DATABASE IF EXISTS DBC");
testConnection.close();
putConnection.close();
}
@Before
public void setUp()
{
}
@After
public void tearDown()
{
}
@Test
public void testClone() throws Exception
{
// after check point 2
assertEquals(5000001, countRows("DB.SC.T"));
assertEquals(1, countRows("DB.SC.T", check2));
check3 = getTime();
run("CREATE TABLE T2 CLONE DB.SC.T" + before(check2));
assertEquals(1, countRows("DB.SC.T2"));
check4 = getTime();
run("INSERT INTO T2 SELECT * FROM T");
assertEquals(5000002, countRows("DB.SC.T2"));
run("DROP TABLE T");
assertEquals(-1, countRows("DB.SC.T"));
check5 = getTime();
run("CREATE TABLE TC CLONE DB.SC.T2" + before(check4));
assertEquals(1, countRows("DB.SC.TC"));
check6 = getTime();
run("CREATE SCHEMA SCC CLONE SC" + before(check4));
assertEquals(1, countRows("DB.SCC.T2"));
assertEquals(5000001, countRows("DB.SCC.T"));
assertEquals(-1, countRows("DB.SCC.TC"));
check7 = getTime();
assertEquals(-1, countRows("DB.SC.T"));
run("USE SCHEMA SC");
run("UNDROP TABLE T");
run("DROP TABLE DB.SCC.T2");
check8 = getTime();
run("CREATE DATABASE DBC CLONE DB" + before(check8));
assertEquals(5000001, countRows("DBC.SC.T"));
assertEquals(5000002, countRows("DBC.SC.T2"));
assertEquals(1, countRows("DBC.SC.TC"));
assertEquals(5000001, countRows("DBC.SCC.T"));
assertEquals(-1, countRows("DBC.SCC.T2"));
assertEquals(-1, countRows("DBC.SCC.TC"));
}
/**
* Return current epoch time
*/
private static long getTime() throws Exception
{
Thread.sleep(1100); // longer sleep
ResultSet rs
= testConnection.createStatement().executeQuery(
"select extract(epoch_millisecond from current_timestamp()) as N from dual");
rs.next();
long time = rs.getLong("N");
return time;
}
/**
* Return -1 for table not present
*/
private static long countRows(String table)
{
return countRows(table, null);
}
private static long countRows(String table, Long time)
{
try
{
String query = "select count(*) as count from "
+ table;
if(time != null)
{
query = query + before(time);
}
ResultSet rs
= testConnection.createStatement().executeQuery(query);
rs.next();
return rs.getLong("COUNT");
}
catch (SQLException ex)
{
return -1L;
}
}
private static void run(String query) throws SQLException
{
testConnection.createStatement().execute(query);
}
private static String before(long time)
{
long seconds = time / 1000;
long millis = time % 1000;
String stamp = seconds + "." + millis;
return " BEFORE(TIMESTAMP => TO_TIMESTAMP(" + stamp + "))";
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy