<commit_before>/*
 * Copyright (C) 2012 The Regents of The University California.
 * All rights reserved.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package shark

import org.scalatest.BeforeAndAfterAll
import org.scalatest.FunSuite

import shark.api.QueryExecutionException


class SQLSuite extends FunSuite with BeforeAndAfterAll {

  val WAREHOUSE_PATH = TestUtils.getWarehousePath()
  val METASTORE_PATH = TestUtils.getMetastorePath()
  val MASTER = "local"

  var sc: SharkContext = _

  override def beforeAll() {
    sc = SharkEnv.initWithSharkContext("shark-sql-suite-testing", MASTER)

    sc.sql("set javax.jdo.option.ConnectionURL=jdbc:derby:;databaseName=" +
        METASTORE_PATH + ";create=true")
    sc.sql("set hive.metastore.warehouse.dir=" + WAREHOUSE_PATH)

    sc.sql("set shark.test.data.path=" + TestUtils.dataFilePath)

    // test
    sc.sql("drop table if exists test")
    sc.sql("CREATE TABLE test (key INT, val STRING)")
    sc.sql("LOAD DATA LOCAL INPATH '${hiveconf:shark.test.data.path}/kv1.txt' INTO TABLE test")
    sc.sql("drop table if exists test_cached")
    sc.sql("CREATE TABLE test_cached AS SELECT * FROM test")

    // test_null
    sc.sql("drop table if exists test_null")
    sc.sql("CREATE TABLE test_null (key INT, val STRING)")
    sc.sql("LOAD DATA LOCAL INPATH '${hiveconf:shark.test.data.path}/kv3.txt' INTO TABLE test_null")
    sc.sql("drop table if exists test_null_cached")
    sc.sql("CREATE TABLE test_null_cached AS SELECT * FROM test_null")

    // clicks
    sc.sql("drop table if exists clicks")
    sc.sql("""create table clicks (id int, click int)
      row format delimited fields terminated by '\t'""")
    sc.sql("""load data local inpath '${hiveconf:shark.test.data.path}/clicks.txt'
      OVERWRITE INTO TABLE clicks""")
    sc.sql("drop table if exists clicks_cached")
    sc.sql("create table clicks_cached as select * from clicks")

    // users
    sc.sql("drop table if exists users")
    sc.sql("""create table users (id int, name string)
      row format delimited fields terminated by '\t'""")
    sc.sql("""load data local inpath '${hiveconf:shark.test.data.path}/users.txt'
      OVERWRITE INTO TABLE users""")
    sc.sql("drop table if exists users_cached")
    sc.sql("create table users_cached as select * from users")
  }

  override def afterAll() {
    sc.stop()
    System.clearProperty("spark.driver.port")
  }

  private def expectSql(sql: String, expectedResults: Array[String], sort: Boolean = true) {
    val results = if (sort) sc.sql(sql).sortWith(_ < _) else sc.sql(sql)
    val expected = if (sort) expectedResults.sortWith(_ < _) else expectedResults
    assert(results.corresponds(expected)(_.equals(_)),
      "In SQL: " + sql + "\n" +
      "Expected: " + expected.mkString("\n") + "; got " + results.mkString("\n"))
  }

  // A shortcut for single row results.
  private def expectSql(sql: String, expectedResult: String) {
    expectSql(sql, Array(expectedResult))
  }

  //////////////////////////////////////////////////////////////////////////////
  // basic SQL
  //////////////////////////////////////////////////////////////////////////////
  test("count") {
    expectSql("select count(*) from test", "500")
    expectSql("select count(*) from test_cached", "500")
  }

  test("filter") {
    expectSql("select * from test where key=100 or key=497",
      Array("100\tval_100", "100\tval_100", "497\tval_497"))
    expectSql("select * from test_cached where key=100 or key=497",
      Array("100\tval_100", "100\tval_100", "497\tval_497"))
  }

  test("count distinct") {
    sc.sql("set mapred.reduce.tasks=3")
    expectSql("select count(distinct key) from test", "309")
    expectSql("select count(distinct key) from test_cached", "309")
    expectSql(
      """|SELECT substr(key,1,1), count(DISTINCT substr(val,5)) from test
         |GROUP BY substr(key,1,1)""".stripMargin,
      Array("0\t1", "1\t71", "2\t69", "3\t62", "4\t74", "5\t6", "6\t5", "7\t6", "8\t8", "9\t7"))
  }

  test("count bigint") {
    sc.sql("drop table if exists test_bigint")
    sc.sql("create table test_bigint (key bigint, val string)")
    sc.sql("""load data local inpath '${hiveconf:shark.test.data.path}/kv1.txt'
      OVERWRITE INTO TABLE test_bigint""")
    sc.sql("drop table if exists test_bigint_cached")
    sc.sql("create table test_bigint_cached as select * from test_bigint")
    expectSql("select val, count(*) from test_bigint_cached where key=484 group by val", "val_484\t1")
  }

  test("limit") {
    assert(sc.sql("select * from test limit 10").length === 10)
    assert(sc.sql("select * from test limit 501").length === 500)
    sc.sql("drop table if exists test_limit0")
    assert(sc.sql("select * from test limit 0").length === 0)
    assert(sc.sql("create table test_limit0 as select * from test limit 0").length === 0)
    assert(sc.sql("select * from test_limit0 limit 0").length === 0)
    assert(sc.sql("select * from test_limit0 limit 1").length === 0)
  }

  //////////////////////////////////////////////////////////////////////////////
  // sorting
  //////////////////////////////////////////////////////////////////////////////

  /* Commenting this out since Linux file systems don't return results alphabetically, while OS X and HDFS does.
  test("full order by") {
    expectSql("select * from users order by id", Array("1\tA", "2\tB", "3\tA"), sort = false)
    expectSql("select * from users order by id desc", Array("3\tA", "2\tB", "1\tA"), sort = false)
    expectSql("select * from users order by name, id", Array("1\tA", "3\tA", "2\tB"), sort = false)
    expectSql("select * from users order by name desc, id desc", Array("2\tB", "3\tA", "1\tA"),
      sort = false)
  }
  */

  test("full order by with limit") {
    expectSql("select * from users order by id limit 2", Array("1\tA", "2\tB"), sort = false)
    expectSql("select * from users order by id desc limit 2", Array("3\tA", "2\tB"), sort = false)
    expectSql("select * from users order by name, id limit 2", Array("1\tA", "3\tA"), sort = false)
    expectSql("select * from users order by name desc, id desc limit 2", Array("2\tB", "3\tA"),
      sort = false)
  }

  //////////////////////////////////////////////////////////////////////////////
  // column pruning
  //////////////////////////////////////////////////////////////////////////////
  test("column pruning filters") {
    expectSql("select count(*) from test_cached where key > -1", "500")
  }

  test("column pruning group by") {
    expectSql("select key, count(*) from test_cached group by key order by key limit 1", "0\t3")
  }

  test("column pruning group by with single filter") {
    expectSql("select key, count(*) from test_cached where val='val_484' group by key", "484\t1")
  }

  test("column pruning aggregate function") {
    expectSql("select val, sum(key) from test_cached group by val order by val desc limit 1",
      "val_98\t196")
  }

  //////////////////////////////////////////////////////////////////////////////
  // map join<commit_msg>use "ignore(...)" instead of commenting out a test.<commit_after>/*
 * Copyright (C) 2012 The Regents of The University California.
 * All rights reserved.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package shark

import org.scalatest.BeforeAndAfterAll
import org.scalatest.FunSuite

import shark.api.QueryExecutionException


class SQLSuite extends FunSuite with BeforeAndAfterAll {

  val WAREHOUSE_PATH = TestUtils.getWarehousePath()
  val METASTORE_PATH = TestUtils.getMetastorePath()
  val MASTER = "local"

  var sc: SharkContext = _

  override def beforeAll() {
    sc = SharkEnv.initWithSharkContext("shark-sql-suite-testing", MASTER)

    sc.sql("set javax.jdo.option.ConnectionURL=jdbc:derby:;databaseName=" +
        METASTORE_PATH + ";create=true")
    sc.sql("set hive.metastore.warehouse.dir=" + WAREHOUSE_PATH)

    sc.sql("set shark.test.data.path=" + TestUtils.dataFilePath)

    // test
    sc.sql("drop table if exists test")
    sc.sql("CREATE TABLE test (key INT, val STRING)")
    sc.sql("LOAD DATA LOCAL INPATH '${hiveconf:shark.test.data.path}/kv1.txt' INTO TABLE test")
    sc.sql("drop table if exists test_cached")
    sc.sql("CREATE TABLE test_cached AS SELECT * FROM test")

    // test_null
    sc.sql("drop table if exists test_null")
    sc.sql("CREATE TABLE test_null (key INT, val STRING)")
    sc.sql("LOAD DATA LOCAL INPATH '${hiveconf:shark.test.data.path}/kv3.txt' INTO TABLE test_null")
    sc.sql("drop table if exists test_null_cached")
    sc.sql("CREATE TABLE test_null_cached AS SELECT * FROM test_null")

    // clicks
    sc.sql("drop table if exists clicks")
    sc.sql("""create table clicks (id int, click int)
      row format delimited fields terminated by '\t'""")
    sc.sql("""load data local inpath '${hiveconf:shark.test.data.path}/clicks.txt'
      OVERWRITE INTO TABLE clicks""")
    sc.sql("drop table if exists clicks_cached")
    sc.sql("create table clicks_cached as select * from clicks")

    // users
    sc.sql("drop table if exists users")
    sc.sql("""create table users (id int, name string)
      row format delimited fields terminated by '\t'""")
    sc.sql("""load data local inpath '${hiveconf:shark.test.data.path}/users.txt'
      OVERWRITE INTO TABLE users""")
    sc.sql("drop table if exists users_cached")
    sc.sql("create table users_cached as select * from users")
  }

  override def afterAll() {
    sc.stop()
    System.clearProperty("spark.driver.port")
  }

  private def expectSql(sql: String, expectedResults: Array[String], sort: Boolean = true) {
    val results = if (sort) sc.sql(sql).sortWith(_ < _) else sc.sql(sql)
    val expected = if (sort) expectedResults.sortWith(_ < _) else expectedResults
    assert(results.corresponds(expected)(_.equals(_)),
      "In SQL: " + sql + "\n" +
      "Expected: " + expected.mkString("\n") + "; got " + results.mkString("\n"))
  }

  // A shortcut for single row results.
  private def expectSql(sql: String, expectedResult: String) {
    expectSql(sql, Array(expectedResult))
  }

  //////////////////////////////////////////////////////////////////////////////
  // basic SQL
  //////////////////////////////////////////////////////////////////////////////
  test("count") {
    expectSql("select count(*) from test", "500")
    expectSql("select count(*) from test_cached", "500")
  }

  test("filter") {
    expectSql("select * from test where key=100 or key=497",
      Array("100\tval_100", "100\tval_100", "497\tval_497"))
    expectSql("select * from test_cached where key=100 or key=497",
      Array("100\tval_100", "100\tval_100", "497\tval_497"))
  }

  test("count distinct") {
    sc.sql("set mapred.reduce.tasks=3")
    expectSql("select count(distinct key) from test", "309")
    expectSql("select count(distinct key) from test_cached", "309")
    expectSql(
      """|SELECT substr(key,1,1), count(DISTINCT substr(val,5)) from test
         |GROUP BY substr(key,1,1)""".stripMargin,
      Array("0\t1", "1\t71", "2\t69", "3\t62", "4\t74", "5\t6", "6\t5", "7\t6", "8\t8", "9\t7"))
  }

  test("count bigint") {
    sc.sql("drop table if exists test_bigint")
    sc.sql("create table test_bigint (key bigint, val string)")
    sc.sql("""load data local inpath '${hiveconf:shark.test.data.path}/kv1.txt'
      OVERWRITE INTO TABLE test_bigint""")
    sc.sql("drop table if exists test_bigint_cached")
    sc.sql("create table test_bigint_cached as select * from test_bigint")
    expectSql("select val, count(*) from test_bigint_cached where key=484 group by val", "val_484\t1")
  }

  test("limit") {
    assert(sc.sql("select * from test limit 10").length === 10)
    assert(sc.sql("select * from test limit 501").length === 500)
    sc.sql("drop table if exists test_limit0")
    assert(sc.sql("select * from test limit 0").length === 0)
    assert(sc.sql("create table test_limit0 as select * from test limit 0").length === 0)
    assert(sc.sql("select * from test_limit0 limit 0").length === 0)
    assert(sc.sql("select * from test_limit0 limit 1").length === 0)
  }

  //////////////////////////////////////////////////////////////////////////////
  // sorting
  //////////////////////////////////////////////////////////////////////////////

  ignore("full order by") {
    expectSql("select * from users order by id", Array("1\tA", "2\tB", "3\tA"), sort = false)
    expectSql("select * from users order by id desc", Array("3\tA", "2\tB", "1\tA"), sort = false)
    expectSql("select * from users order by name, id", Array("1\tA", "3\tA", "2\tB"), sort = false)
    expectSql("select * from users order by name desc, id desc", Array("2\tB", "3\tA", "1\tA"),
      sort = false)
  }

  test("full order by with limit") {
    expectSql("select * from users order by id limit 2", Array("1\tA", "2\tB"), sort = false)
    expectSql("select * from users order by id desc limit 2", Array("3\tA", "2\tB"), sort = false)
    expectSql("select * from users order by name, id limit 2", Array("1\tA", "3\tA"), sort = false)
    expectSql("select * from users order by name desc, id desc limit 2", Array("2\tB", "3\tA"),
      sort = false)
  }

  //////////////////////////////////////////////////////////////////////////////
  // column pruning
  //////////////////////////////////////////////////////////////////////////////
  test("column pruning filters") {
    expectSql("select count(*) from test_cached where key > -1", "500")
  }

  test("column pruning group by") {
    expectSql("select key, count(*) from test_cached group by key order by key limit 1", "0\t3")
  }

  test("column pruning group by with single filter") {
    expectSql("select key, count(*) from test_cached where val='val_484' group by key", "484\t1")
  }

  test("column pruning aggregate function") {
    expectSql("select val, sum(key) from test_cached group by val order by val desc limit 1",
      "val_98\t196")
  }

  //////////////////////////////////////////////////////////////////////////////
  // map join
{'fraction_non_alphanumeric': 0.20486342438374416, 'fraction_numerical': 0.02318454363757495, 'mean_word_length': 2.6434466019417475, 'pattern_counts': {'":': 0, '<': 7, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, '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}}
{'dir': 'git-commits-cleaned', 'id': '4920840', 'n_tokens_mistral': 4791, 'n_tokens_neox': 4499, 'n_words': 1373}