net.snowflake.client.IngestFilesTester Maven / Gradle / Ivy

Go to download
/*
 * Copyright (c) 2012, 2013 Snowflake Computing Inc. All right reserved.
 */

package net.snowflake.client;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.google.common.base.Joiner;
import com.google.common.base.Preconditions;
import com.google.common.collect.Sets;
import com.google.common.net.MediaType;
import net.snowflake.client.core.SFSession;
import net.snowflake.client.ingest.IngestHistoryResponse;
import net.snowflake.client.ingest.avro.Row;
import org.apache.avro.file.DataFileWriter;
import org.apache.avro.io.DatumWriter;
import org.apache.avro.specific.SpecificDatumWriter;
import org.apache.commons.codec.binary.Base64;
import org.apache.http.Header;
import org.apache.http.HttpEntity;
import org.apache.http.HttpHeaders;
import org.apache.http.HttpResponse;
import org.apache.http.StatusLine;
import org.apache.http.client.ResponseHandler;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.client.methods.HttpUriRequest;
import org.apache.http.client.utils.URIBuilder;
import org.apache.http.entity.StringEntity;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClientBuilder;
import org.apache.http.util.EntityUtils;
import org.jose4j.jws.AlgorithmIdentifiers;
import org.jose4j.jws.JsonWebSignature;
import org.jose4j.jwt.JwtClaims;
import org.jose4j.lang.JoseException;

import java.io.IOException;
import java.io.OutputStream;
import java.io.Writer;
import java.net.URI;
import java.net.URISyntaxException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.security.Key;
import java.security.KeyFactory;
import java.security.KeyPair;
import java.security.KeyPairGenerator;
import java.security.NoSuchAlgorithmException;
import java.security.NoSuchProviderException;
import java.security.PublicKey;
import java.security.SecureRandom;
import java.security.spec.InvalidKeySpecException;
import java.security.spec.X509EncodedKeySpec;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.UUID;
import java.util.concurrent.Callable;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
import java.util.concurrent.ThreadLocalRandom;
import java.util.concurrent.atomic.AtomicInteger;

public class IngestFilesTester
{

  private static final String JSON_UTF8_CT = MediaType.JSON_UTF_8.toString();

  public enum FileFormat
  {
    CSV("csv"),
    JSON("json"),
    AVRO("avro"),
    XML("xml");

    final private String formatNameString;

    FileFormat(String formatString)
    {
      this.formatNameString = formatString;
    }

    public String getFormatName()
    {
      return this.formatNameString;
    }
  }

  protected final String RND_PAD =
      Math.abs(ThreadLocalRandom.current().nextLong()) +
          "" + System.currentTimeMillis();

  protected final String SCHEMA = "testschema".toUpperCase() + RND_PAD;
  protected final String DATABASE = "testdb".toUpperCase() + RND_PAD;

  protected final String INGEST_TBL = "ing_t";
  protected final String PIPE_NAME = "ingest_pipe";
  protected final String STAGE_NAME = "ingest_stage";

  protected final String fqTableName =
      DATABASE + "." + SCHEMA + "." + quote(INGEST_TBL);

  protected final String fqPipeName =
      DATABASE + "." + SCHEMA + "." + quote(PIPE_NAME);


  protected final Connection connection;
  protected final CloseableHttpClient httpClient;
  protected final Path tmpIngestStage = createTempStageDir();
  protected final ObjectMapper objectMapper;

  private final ExecutorService executorService =
      Executors.newCachedThreadPool();


  protected Path createTempStageDir()
  {
    final String base = "/tmp/ingest_stage";
    final Path basedir = Paths.get(base);
    try
    {
      Files.createDirectories(Paths.get(base));
      final String prefix = String.valueOf(RND_PAD + "_");
      return Files.createTempDirectory(basedir, prefix);
    }
    catch (IOException e)
    {
      throw new IllegalStateException("create temp dir failed", e);
    }
  }


  protected IngestFilesTester(Connection connection,
                            CloseableHttpClient httpClient)
  {
    this.connection = connection;
    this.httpClient = httpClient;
    objectMapper = new ObjectMapper();
  }

  public static CloseableHttpClient createHttpClient()
  {
    return HttpClientBuilder.create().build();
  }

  public static void main(String[] args)
      throws Exception
  {

    final int inter_batch_delay = 250;
    final int history_poll_interval = 3000;
    final int batch_size = 1;
    final int max_rows_per_file = 5;
    final int push_threads = 2;
    final int batch_count = push_threads * 240;

    fullTest(inter_batch_delay, history_poll_interval, batch_size,
             max_rows_per_file, push_threads, batch_count, true);

    say("System.exit(0)");
    System.exit(0);
  }


  static boolean fullTest(int inter_batch_delay,
                          int history_poll_interval,
                          int batch_size,
                          int max_rows_per_file,
                          int push_threads,
                          int batch_count,
                          boolean encrypt_fdn_files)
      throws Exception
  {

    boolean success = false;

    enableIngestService();
    enableIngestForAccount(true);

    //TODO: stop making this global
    enableKeyPairAuthn();

    try (Connection connection = getConnection();
        CloseableHttpClient httpClient = createHttpClient())
    {
      final IngestFilesTester test =
          new IngestFilesTester(connection, httpClient);
      success =
          test.doTest(FileFormat.CSV, inter_batch_delay, history_poll_interval,
                      batch_size, max_rows_per_file, push_threads, batch_count,
                      encrypt_fdn_files);
    }

    //TODO: we need to test the failure cases!!

    say("test complete");

    return success;
  }

  protected static final class SecurityState implements AutoCloseable
  {
    private final KeyPair pair;
    private final KeyFactory keyFactory;
    private final String jwtToken;

    private static final String ALGORITHM = "RSA";

    SecurityState(String account, String user, KeyPair pair)
        throws NoSuchAlgorithmException
    {
      this.pair = Preconditions.checkNotNull(pair);
      keyFactory = KeyFactory.getInstance(ALGORITHM);
      jwtToken = generateJwtToken(account, user, pair);
    }

    public static SecurityState create(String account, String user)
        throws NoSuchAlgorithmException, NoSuchProviderException
    {
      KeyPairGenerator keyGen = KeyPairGenerator.getInstance(ALGORITHM);
      SecureRandom random = SecureRandom.getInstance("SHA1PRNG", "SUN");
      keyGen.initialize(2048, random);
      KeyPair pair = keyGen.generateKeyPair();
      return new SecurityState(account, user, pair);
    }

    @Override
    public void close() throws Exception
    {
    }

    public String getPublicKeyString() throws InvalidKeySpecException
    {
      final PublicKey pk = pair.getPublic();
      X509EncodedKeySpec spec =
          keyFactory.getKeySpec(pk, X509EncodedKeySpec.class);
      return Base64.encodeBase64String(spec.getEncoded());
    }

    public String getJwtToken()
    {
      return jwtToken;
    }

    public static String generateJwtToken(String account,
                                           String user,
                                           KeyPair pair)
    {
      // Create the Claims, which will be the content of the JWT
      JwtClaims claims = new JwtClaims();

      //seems we need upper case...
      final String issuer = (account + "." + user).toUpperCase();

      claims.setIssuer(issuer);  // who creates the token and signs it

      //snowflake will expire token in 1 hour anyway.
      final int ttl = 59;

      //time when the token will expire
      claims.setExpirationTimeMinutesInTheFuture(ttl);

      //when the token was issued/created (now)
      claims.setIssuedAtToNow();

      // A JWT is a JWS and/or a JWE with JSON claims as the payload.
      // In this case it is a JWS so we create a JsonWebSignature object.
      JsonWebSignature jws = new JsonWebSignature();

      // The payload of the JWS is JSON content of the JWT Claims
      final String json = claims.toJson();
      //System.out.println("**** json = " + json);
      jws.setPayload(json);

      // The JWT is signed using the private key
      final Key key = pair.getPrivate();
      jws.setKey(key);

      // Set the signature algorithm on the JWT/JWS that
      // will integrity protect the claims
      jws.setAlgorithmHeaderValue(AlgorithmIdentifiers.RSA_USING_SHA256);

      // Sign the JWS and produce the compact serialization or the complete
      // JWT/JWS representation, which is a string consisting
      // of three dot ('.') separated base64url-encoded parts
      // in the form Header.Payload.Signature
      String jwt;
      try
      {
        jwt = jws.getCompactSerialization();
      }
      catch (JoseException e)
      {
        throw new RuntimeException("failed to generate JWT token", e);
      }

      //System.out.println("@@@@@@ jwt = " + jwt);


      return jwt;
    }
  }

  public static void enableIngestServiceOld() throws SQLException, IOException
  {
    try (Connection connection = getAdminConnection();
        CloseableHttpClient httpClient = createHttpClient())
    {
      final IngestFilesTester test =
          new IngestFilesTester(connection, httpClient);
      test.doQuery("ALTER system SET INGEST_ACTIVE_CLUSTER=\"default\"");
    }
  }

  public static void enableIngestService() throws SQLException, IOException
  {
    try (Connection connection = getAdminConnection();
        CloseableHttpClient httpClient = createHttpClient())
    {
      final IngestFilesTester test =
          new IngestFilesTester(connection, httpClient);

      final Set gs_ids = getGsIds(test);

      final String ids = quote(Joiner.on(',').join(gs_ids));
      test.doQuery("alter system set INGEST_ACTIVE_INSTANCES_OVERRIDE=" + ids);
    }
  }

  //NOTE: this does not completely disable ingest service, just makes
  //it so that no nodes will work on ingest queues.
  public static void disableIngestService() throws SQLException, IOException
  {
    try (Connection connection = getAdminConnection();
        CloseableHttpClient httpClient = createHttpClient())
    {
      final IngestFilesTester test =
          new IngestFilesTester(connection, httpClient);

      test.doQuery("alter system set INGEST_ACTIVE_INSTANCES_OVERRIDE=\"\"");
    }
  }

  private static Set getGsIds(IngestFilesTester tester) throws SQLException
  {
    final Set gs_ids = new HashSet<>();
    try (Statement statement = tester.connection.createStatement())
    {
      try (ResultSet resultSet = statement.executeQuery("show gsinstances"))
      {
        while (resultSet.next())
        {
          final long gs_id = resultSet.getLong(1);
          gs_ids.add(gs_id);
        }
      }
    }
    return gs_ids;
  }

  public static void enableKeyPairAuthn() throws SQLException, IOException
  {
    try (Connection connection = getAdminConnection();
        CloseableHttpClient httpClient = createHttpClient())
    {
      final IngestFilesTester test =
          new IngestFilesTester(connection, httpClient);
      test.doQuery("ALTER system SET ENABLE_KEY_PAIR_AUTH=true");
    }
  }

  protected static void enableEncryption(boolean enabled)
      throws SQLException, IOException
  {
    try (Connection connection = getAdminConnection();
        CloseableHttpClient httpClient = createHttpClient())
    {
      final IngestFilesTester test =
          new IngestFilesTester(connection, httpClient);
      test.doQuery("alter account " + TestConnectionUtil.ACCOUNT +
                       " set encrypt_data = " + enabled);
    }
  }

  public static void enableIngestForAccount(boolean enabled)
      throws SQLException, IOException
  {
    try (Connection connection = getAdminConnection();
        CloseableHttpClient httpClient = createHttpClient())
    {
      final IngestFilesTester test =
          new IngestFilesTester(connection, httpClient);
      test.doQuery("alter account " + TestConnectionUtil.ACCOUNT +
                       " set ENABLE_INGEST_FEATURE = " + enabled);
    }
  }

  public static void disableIngestServiceOld() throws SQLException, IOException
  {
    try (Connection connection = getAdminConnection();
        CloseableHttpClient httpClient = createHttpClient())
    {
      final IngestFilesTester test =
          new IngestFilesTester(connection, httpClient);
      test.doQuery("ALTER system SET INGEST_ACTIVE_CLUSTER=default");
    }
  }

  private boolean doTest(FileFormat fileFormat,
                         int inter_batch_delay,
                         int history_poll_interval,
                         int batch_size,
                         int max_rows_per_file,
                         int push_threads,
                         int batch_count,
                         boolean encrypt_fdn_files)
      throws Exception
  {
    doQuery("create database " + DATABASE);
    doQuery("use database " + DATABASE);
    doQuery("create schema " + SCHEMA);
    doQuery("use schema " + SCHEMA);

    final String insert_role = "API_INSERT";
    doQuery("use role accountadmin");
    doQuery("create or replace role " + insert_role);


    final String user = "user" + RND_PAD;
    doQuery("create user " + user + " default_role = " + insert_role +
                " password=\"" + TestConnectionUtil.PASSWORD + "\"");
    doQuery("grant role " + insert_role + " to user " + user);


    final String account = TestConnectionUtil.ACCOUNT;

    say("begin setup security");
    final SecurityState securityState = SecurityState.create(account, user);
    say("end setup security");

    String pk = securityState.getPublicKeyString();
    assert pk != null;
    assert !pk.isEmpty();

    doQuery("alter user " + user +
                " set RSA_PUBLIC_KEY='" + pk + "'");


    final String truthTable = quote(INGEST_TBL + "_TRUTH");


//    final SessionUtil.LoginOutput loginOutput = login();
//    final String sessionToken = loginOutput.getSessionToken();

    setupTables(fileFormat, truthTable);



    doQuery("grant INSERT on " + quote(INGEST_TBL) + " to ROLE " + insert_role);

    //for read history.
    doQuery("grant SELECT on " + quote(INGEST_TBL) + " to ROLE " + insert_role);

    doQuery("grant USAGE on STAGE " + quote(STAGE_NAME)
                + " to ROLE " + insert_role);

    //TODO: avoid need for grant ALL
    doQuery("grant ALL on DATABASE " + DATABASE +
                " to ROLE " + insert_role);
    doQuery("grant ALL on SCHEMA " + SCHEMA +
                " to ROLE " + insert_role);

//    doQuery("grant USAGE on DATABASE " + DATABASE +
//                " to ROLE " + insert_role);
//    doQuery("grant USAGE on SCHEMA " + SCHEMA +
//                " to ROLE " + insert_role);
//

    createPipe(user);

    if (encrypt_fdn_files) {
      enableEncryption(true);
    }

    try
    {
      runTest(fileFormat,
              inter_batch_delay,
              batch_count,
              batch_size,
              max_rows_per_file,
              securityState.getJwtToken(),
              push_threads,
              history_poll_interval);

      return checkWithCopy(truthTable);
    }
    finally
    {
      doQuery("drop database " + DATABASE);
      disableIngestService();

      if (encrypt_fdn_files) {
        //NOTE: assumes default for the account is false!
        enableEncryption(false);
      }

    }
  }

  public void createPipe(String user) throws SQLException
  {
    //creating pipe as the ingest user since
    //we don't have GRANT for PIPE yet.
    try (Connection user_conn =
        TestConnectionUtil.getConnection(user, TestConnectionUtil.ACCOUNT))
    {
      final IngestFilesTester user_tester =
          new IngestFilesTester(user_conn, this.httpClient);

      user_tester.doQuery("use database " + DATABASE);
      user_tester.doQuery("use schema " + SCHEMA);

      final String query =
          "create pipe " + quote(PIPE_NAME) + " as COPY into " +
              quote(INGEST_TBL) + " from @" + quote(STAGE_NAME);
      user_tester.doQuery(query);
    }
  }

  private void runTest(final FileFormat fileFormat,
                       final int inter_batch_delay,
                       final int batch_count,
                       final int batch_size,
                       final int max_rows_per_file,
                       final String jwtToken,
                       final int push_threads,
                       final int history_poll_interval)
      throws IOException, URISyntaxException, InterruptedException,
             ExecutionException
  {

    final ConcurrentHashMap pushed = new ConcurrentHashMap<>();
    final ConcurrentHashMap seen = new ConcurrentHashMap<>();
    final AtomicInteger batchesRemaining = new AtomicInteger(batch_count);

    Callable pusher = new Callable()
    {
      @Override
      public Integer call() throws Exception
      {
        final ThreadLocalRandom rnd = ThreadLocalRandom.current();
        int cnt = 0;
        while (batchesRemaining.decrementAndGet() > 0)
        {

          final Collection files =
              createTempFiles(batch_size, max_rows_per_file, fileFormat);


          final Boolean success = postFiles(files, jwtToken);
          if (success)
          {
            cnt += files.size();
            for (Path file : files)
            {
              final String f = file.getFileName().toString();
              pushed.put(f, f);
            }
          }
          else
          {
            throw new IllegalStateException("post failed");
          }

          //and random amout up to 10% to desync threads...
          final int sl = inter_batch_delay +
              rnd.nextInt(1 + inter_batch_delay / 10);
          Thread.sleep(sl);
        }
        return cnt;
      }
    };

    List> pusher_futures = new ArrayList<>();
    for (int i = 0; i < push_threads; i++)
    {
      final Future f = executorService.submit(pusher);
      pusher_futures.add(f);
    }


    Callable watcher = new Callable()
    {
      @Override
      public Integer call() throws Exception
      {
        while (batchesRemaining.get() > 0 || seen.size() < pushed.size())
        {
          try
          {
            final Collection files_seen = getHistory(jwtToken);
            say("saw " + files_seen.size() + " files from history");
            for (String f : files_seen)
            {
              final URI file_uri = URI.create(f);
              String p = file_uri.getPath();
              if (p.startsWith("/"))
              {
                p = p.substring(1);
              }
              seen.put(p, p);
            }
          }
          catch (Exception e)
          {
            //our test might involve restarting GS so we want to just keep
            //trying...
            say("got error fetching history: " + e);
          }

          Thread.sleep(history_poll_interval);

          final Sets.SetView missing =
              Sets.difference(pushed.keySet(), seen.keySet());
          say("## waiting on " + missing.size() + " files: " + missing);
        }

        return 0; //unused for now.
      }
    };

    final Future watcher_future = executorService.submit(watcher);


    //we call get() on futures to surface any exceptions.

    for (Future pusher_future : pusher_futures)
    {
      final Integer cnt = pusher_future.get();
    }

    say("%%%%%% pushers done. file cnt = " + pushed.size());

    final Integer ign = watcher_future.get();

    if (!seen.equals(pushed))
    {
      say("pushed " + pushed);
      say("seen   " + seen);
      throw new IllegalStateException("mismatch");
    }

    say(pushed.size() + " files ingested");
  }

  //returns true if check was a success
  public boolean checkWithCopy(String truthTable) throws SQLException
  {
    doQuery("copy into " + truthTable + " from @" + quote(STAGE_NAME));

    // Check if tha ingest table has the same rows as the truth table. If it
    // does then cnt should be 0
    int cnt =
        doQuery(
            "(select * from " + quote(INGEST_TBL) + " except select * from " +
                truthTable + ") UNION ALL (select * from " + truthTable +
                " except select * from " + quote(INGEST_TBL) + ")");

    say("number of different rows between the " +
            "truth table and the ingest table: " + cnt);


    if (cnt == 0)
    {
      say("*** SUCCESS ***  no diff");
    }
    else
    {
      say("!!! FAILURE !!! " + cnt + " diffs found");
      doQuery("select count(*) from " + truthTable);
      doQuery("select count(*) from " + quote(INGEST_TBL));
    }
    return cnt == 0;
  }

  private void setupTables(FileFormat fileFormat,
                           String truthTable) throws SQLException
  {
    switch (fileFormat)
    {
      case JSON:
        doQuery("create or replace stage " + quote(STAGE_NAME) +
                    " url='file://" + tmpIngestStage + "' " +
                    " file_format=(type=json)");
        doQuery("create or replace table " + quote(INGEST_TBL) +
                    " (v variant)");
        doQuery("create or replace table " + truthTable +
                    " (v variant)");
        break;
      case CSV:
      default:
        doQuery("create or replace stage " + quote(STAGE_NAME) +
                    " url='file://" + tmpIngestStage + "'");
        doQuery("create or replace table " + quote(INGEST_TBL) +
                    " (row_id int,row_str string,num int, src string)");
        doQuery("create or replace table " + truthTable +
                    " (row_id int,row_str string,num int, src string)");
    }
  }

  private Collection getHistory(String jwtToken)
      throws URISyntaxException, IOException
  {
    final URI reportEndpoint = historyReportEndpoint();
    HttpGet httpGet = new HttpGet(reportEndpoint);
    addAuthnHeader(httpGet, jwtToken);

    final ResponseHandler> rh =
        new ResponseHandler>()

        {
          @Override
          public Collection handleResponse(HttpResponse response)
              throws IOException
          {
            final StatusLine statusLine = response.getStatusLine();
            final int statusCode = statusLine.getStatusCode();
            if (statusCode == 200)
            {
              final IngestHistoryResponse report =
                  unmarshallHistory(response);

              if (report == null)
              {
                return Collections.emptyList();
              }

              if (!report.completeResult)
              {
                //consider throwing...
                say("INCOMPLETE_REPORT!!");
              }

              final List files1 =
                  report.files;
              Set seen = new HashSet<>();
              for (IngestHistoryResponse.FileEntry entry : files1)
              {
                if (entry.complete)
                {
                  seen.add(entry.path);
                }
              }

              final Map statistics = report.statistics;
              if (statistics != null && !statistics.isEmpty())
              {
                say("report stats: " + statistics);
              }
              return seen;
            }
            else
            {
              final HttpEntity entity = response.getEntity();
              final String json = EntityUtils.toString(entity);
              say("!!! ERROR getting report from " + reportEndpoint +
                      " : " + json);
              return Collections.emptyList();
            }
          }
        };

    return httpClient.execute(httpGet, rh);
  }

  /*
  private IngestHistoryResponse unmarshallHistory(
      HttpResponse response) throws IOException
  {
    //TODO: avoid extra buffering.
    final HttpEntity entity = response.getEntity();
    final Header ct = response.getLastHeader(HttpHeaders.CONTENT_TYPE);
    final MediaType mt = MediaType.parse(ct.getValue());
    final MediaType jsonUtf8 = MediaType.JSON_UTF_8;
    if (mt.type().equals(jsonUtf8.type()) &&
        mt.subtype().equals(jsonUtf8.subtype()))
    {
      final InputStream content = entity.getContent();
      if (content == null)
      {
        throw new IllegalStateException("no content!");
      }
      return objectMapper.readValue(content, IngestHistoryResponse.class);
    }
    else
    {
      throw new IllegalStateException("unknown content type: " + mt);
    }
  }
  */

  private IngestHistoryResponse unmarshallHistory(
      HttpResponse response) throws IOException
  {
    //TODO: avoid extra buffering.
    final HttpEntity entity = response.getEntity();
    final Header ct = response.getLastHeader(HttpHeaders.CONTENT_TYPE);
    final MediaType mt = MediaType.parse(ct.getValue());
    final MediaType jsonUtf8 = MediaType.JSON_UTF_8;
    if (mt.type().equals(jsonUtf8.type()) &&
        mt.subtype().equals(jsonUtf8.subtype()))
    {
      final String json = EntityUtils.toString(entity);
      say("history_response: \n" + json);
      return objectMapper.readValue(json, IngestHistoryResponse.class);
    }
    else
    {
      throw new IllegalStateException("unknown content type: " + mt);
    }
  }

  public Collection createTempFiles(int num_files, int max_rows,
                                           FileFormat fileFormat)
      throws IOException
  {
    final ThreadLocalRandom rnd = ThreadLocalRandom.current();

    List files = new ArrayList<>(num_files);
    for (int i = 0; i < num_files; i++)
    {
      final int row_cnt = 1 + rnd.nextInt(max_rows);
      final Path tempFile;
      switch (fileFormat)
      {
        case JSON:
          tempFile = createTempJson(row_cnt);
          break;
        case XML:
          tempFile = createTempXML(row_cnt);
          break;
        case AVRO:
          tempFile = createTempAVRO(row_cnt);
          break;
        case CSV:
        default:
          tempFile = createTempCsv(row_cnt);
      }
      files.add(tempFile);
    }
    return files;
  }

  private Boolean postFiles(Collection csv_files, String jwt_token)
      throws Exception
  {
    final URI insert_endpoint = insertFilesEndpoint();
    say("pushing " + csv_files.size() + " files to " + insert_endpoint);
    final HttpPost post = new HttpPost(insert_endpoint);

    addAuthnHeader(post, jwt_token);

    final String body = createPostJsonBody(csv_files);

    final StringEntity entity = new StringEntity(body, StandardCharsets.UTF_8);
    entity.setContentType(JSON_UTF8_CT);
    post.setEntity(entity);

    final ResponseHandler handler = new ResponseHandler()
    {
      @Override
      public Boolean handleResponse(HttpResponse response)
          throws IOException
      {
        final StatusLine statusLine = response.getStatusLine();
        final int statusCode = statusLine.getStatusCode();
        final HttpEntity responseEntity = response.getEntity();
        final String str = EntityUtils.toString(responseEntity);
        if (statusCode == 200)
        {
          say("insert returned: " + str);
          return true;
        }
        else
        {
          say("postFiles request failed got: " + statusLine + "\n:" + str);
          return false;
        }
      }
    };

    final int attempts = 60;

    for (int i = 1; i <= attempts; i++)
    {
      try
      {
        final Boolean ok = httpClient.execute(post, handler);
        assert ok != null;
        if (ok)
        {
          return true; //else retry
        }
      }
      catch (Exception e)
      {
        say("got error " + e +
                " during file push on attempt " + i + " of " + attempts);

      }

      Thread.sleep(1000);
    }

    throw new IllegalStateException("giving up on post");
  }

  //body is newline separated filenames
  private static String createPostBody(Collection csv_files)
  {
    StringBuilder sb = new StringBuilder(csv_files.size() * 10);
    for (Path csv_file : csv_files)
    {
      sb.append(csv_file.getFileName().toString());
      sb.append('\n');
    }
    return sb.toString();
  }

  private String createPostJsonBody(Collection csv_files)
      throws IOException
  {
    IngestPushFilesRequest req = new IngestPushFilesRequest();
    final List files =
        new ArrayList<>(csv_files.size());

    for (Path csv_file : csv_files)
    {
      final IngestPushFilesRequest.IngestFile file =
          new IngestPushFilesRequest.IngestFile();
      file.path = csv_file.getFileName().toString();
      file.size = Files.size(csv_file);
      files.add(file);
    }

    req.files = files;

    return this.objectMapper.writeValueAsString(req);
  }

  private static void addAuthnHeader(HttpUriRequest request,
                                     String jwt_token)
  {
    request.setHeader(SFSession.SF_HEADER_AUTHORIZATION,
                      "Bearer " + jwt_token);
  }

  private URI insertFilesEndpoint() throws URISyntaxException
  {
    return insertFilesEndpoint(fqPipeName);
  }

  public static URI insertFilesEndpoint(String fqPipeName) throws URISyntaxException
  {
    URIBuilder bldr = new URIBuilder();
    bldr.setScheme("http");
    bldr.setHost(TestConnectionUtil.HOST);
    bldr.setPort(TestConnectionUtil.PORT);


    bldr.setParameter("requestId", UUID.randomUUID().toString());

    final String path =
        String.format("/v1/data/pipes/%s/insertFiles",
                      fqPipeName);

    bldr.setPath(path);

    return bldr.build();
  }

  protected static String quote(String str)
  {
    return '"' + str + '"';
  }

  private URI historyReportEndpoint() throws URISyntaxException
  {
    return historyReportEndpoint(fqPipeName);
  }

  public static URI historyReportEndpoint(String fqPipeName)
      throws URISyntaxException
  {
    URIBuilder bldr = new URIBuilder();
    bldr.setScheme("http");
    bldr.setHost(TestConnectionUtil.HOST);
    bldr.setPort(TestConnectionUtil.PORT);

    bldr.setParameter("requestId", UUID.randomUUID().toString());

    final String path =
        String.format("/v1/data/pipes/%s/insertReport",
                      fqPipeName);

    bldr.setPath(path);

    return bldr.build();
  }

  private Path createTempJson(int rows) throws IOException
  {
    final ThreadLocalRandom rnd = ThreadLocalRandom.current();

    final Path json = Files.createTempFile(tmpIngestStage, "data", ".json");

    try (Writer w = Files.newBufferedWriter(json, StandardCharsets.UTF_8))
    {
      for (int i = 0; i < rows; i++)
      {
        w.write("{\"row-" + String.valueOf(i) + "\":\""
                    + String.valueOf(rnd.nextLong()) + "\"}");
        w.write("\n");
      }
    }
    return json;
  }

  private Path createTempXML(int rows) throws IOException
  {
    final ThreadLocalRandom rnd = ThreadLocalRandom.current();

    final Path xml = Files.createTempFile(tmpIngestStage, "data", ".xml");

    try (Writer w = Files.newBufferedWriter(xml, StandardCharsets.UTF_8))
    {
      for (int i = 0; i < rows; i++)
      {
        w.write(""
            + String.valueOf(rnd.nextLong()) + "");
        w.write("\n");
      }
    }
    return xml;
  }

  private Path createTempAVRO(int rows) throws IOException
  {
    final ThreadLocalRandom rnd = ThreadLocalRandom.current();

    final Path avro = Files.createTempFile(tmpIngestStage, "data", ".avro");

    DatumWriter rowDatumWriter = new SpecificDatumWriter<>(Row.class);
    DataFileWriter fileWriter = new DataFileWriter<>(rowDatumWriter);

    OutputStream out = Files.newOutputStream(avro);
    fileWriter.create(Row.getClassSchema(), out);

    for (int i = 0; i < rows; i++)
    {
      fileWriter.append(new Row(rnd.nextInt(), "row" + String.valueOf(rnd.nextInt())));
    }
    fileWriter.close();

    return avro;
  }

  public Path createTempCsv(int rows) throws IOException
  {
    final ThreadLocalRandom rnd = ThreadLocalRandom.current();

    final Path csv = Files.createTempFile(tmpIngestStage, "data", ".csv");
    final String fileName = csv.getFileName().toString();

    try (Writer w = Files.newBufferedWriter(csv, StandardCharsets.UTF_8))
    {
      for (int i = 0; i < rows; i++)
      {
        w.write(String.valueOf(i));
        w.write(",");
        w.write("row-");
        w.write(String.valueOf(i));
        w.write(",");
        w.write(String.valueOf(rnd.nextLong()));
        w.write(",");
        w.write(fileName);
        w.write("\n");
      }
    }
    return csv;
  }

  public int doQuery(String query)
      throws SQLException
  {
    return doQuery(query, connection);
  }

  public int doQuery(String query,
                     Connection connection)
      throws SQLException
  {
    say("query: " + query);
    try (Statement statement = connection.createStatement())
    {
      try (ResultSet resultSet = statement.executeQuery(query))
      {
        return dumpResultSet(resultSet);
      }
    }

  }

  private static int dumpResultSet(ResultSet resultSet) throws SQLException
  {
    final ResultSetMetaData md = resultSet.getMetaData();
    final int col_cnt = md.getColumnCount();
    int row_cnt = 0;
    while (resultSet.next())
    {
      final List