Java SparkSession.sql方法代码示例

import org.apache.spark.sql.SparkSession; //导入方法依赖的package包/类
@Override
public Object execute(SparkSession sparkSession, ActionStatement actionStatement, CredentialProvider credentialManager) {

    String filePath = actionStatement.getParamValues().get(0).getValue().toString();
    String saveModeStr = actionStatement.getParamValues().get(1).getValue().toString();
    String dfTableName = actionStatement.getParamValues().get(2).getValue().toString();

    SaveMode saveMode = SaveMode.valueOf(saveModeStr);

    String sql = String.format("select * from %s", dfTableName);
    logger.info(String.format("Running sql [%s] to get data and then save it", sql));
    Dataset<Row> df = sparkSession.sql(sql);

    logger.info(String.format("Saving to csv %s, saveMode: %s", filePath, saveMode));
    df.coalesce(1).write().mode(saveMode).option("header", "false").csv(filePath);
    logger.info(String.format("Saved to csv %s, saveMode: %s", filePath, saveMode));
    return null;
}
 

import org.apache.spark.sql.SparkSession; //导入方法依赖的package包/类
@Override
public Object execute(SparkSession sparkSession, ActionStatement actionStatement, CredentialProvider credentialManager) {

    String filePath = actionStatement.getParamValues().get(0).getValue().toString();
    String saveModeStr = actionStatement.getParamValues().get(1).getValue().toString();
    String dfTableName = actionStatement.getParamValues().get(2).getValue().toString();

    SaveMode saveMode = SaveMode.valueOf(saveModeStr);

    String sql = String.format("select * from %s", dfTableName);
    logger.info(String.format("Running sql [%s] to get data and then save it", sql));
    Dataset<Row> df = sparkSession.sql(sql);

    logger.info(String.format("Saving to json %s, saveMode: %s", filePath, saveMode));
    df.coalesce(1).write().mode(saveMode).json(filePath);
    logger.info(String.format("Saved to json %s, saveMode: %s", filePath, saveMode));
    return null;
}
 

import org.apache.spark.sql.SparkSession; //导入方法依赖的package包/类
@Override
public Object execute(SparkSession sparkSession, ActionStatement actionStatement, CredentialProvider credentialManager) {

    String filePath = actionStatement.getParamValues().get(0).getValue().toString();
    String saveModeStr = actionStatement.getParamValues().get(1).getValue().toString();
    String dfTableName = actionStatement.getParamValues().get(2).getValue().toString();

    SaveMode saveMode = SaveMode.valueOf(saveModeStr);

    String sql = String.format("select * from %s", dfTableName);
    logger.info(String.format("Running sql [%s] to get data and then save it", sql));
    Dataset<Row> df = sparkSession.sql(sql);

    logger.info(String.format("Saving to parquet %s, saveMode: %s", filePath, saveMode));
    df.coalesce(1).write().mode(saveMode).parquet(filePath);
    logger.info(String.format("Saved to parquet %s, saveMode: %s", filePath, saveMode));
    return null;
}
 

import org.apache.spark.sql.SparkSession; //导入方法依赖的package包/类
public static void main(String[] args) {
	System.setProperty("hadoop.home.dir", "C:\\softwares\\Winutils");
	SparkSession sparkSession = SparkSession.builder()
			.master("local")
			.appName("CSV Read Example")
			.config("spark.sql.warehouse.dir", "file:////C:/Users/sgulati/spark-warehouse")
			.getOrCreate();
	
	Dataset<Row> csv = sparkSession.read().format("com.databricks.spark.csv").option("header","true")
			.load("C:\\Users\\sgulati\\Documents\\my_docs\\book\\testdata\\emp.csv");
	
	csv.createOrReplaceTempView("test");
	Dataset<Row> sql = sparkSession.sql("select * from test");
	sql.collectAsList();
}
 

import org.apache.spark.sql.SparkSession; //导入方法依赖的package包/类
/**
 * Creates a table of ancestor records partitioned by uri and version.
 *
 * @param spark the spark session
 * @param tableName the name of the ancestors table
 * @param location the location to store the table, or null to create a Hive-managed table
 * @throws IllegalArgumentException if the table name or location are malformed
 */
private static void createAncestorsTable(SparkSession spark, String tableName, String location) {

  if (!TABLE_NAME_PATTERN.matcher(tableName).matches()) {
    throw new IllegalArgumentException("Invalid table name: " + tableName);
  }

  // Hive will check for well-formed paths, so we just ensure a user isn't attempting to inject
  // additional SQL into the statement
  if (location != null && location.contains(";")) {
    throw new IllegalArgumentException("Invalid path for values table: " + location);
  }

  StringBuilder builder = new StringBuilder();

  if (location != null) {

    builder.append("CREATE EXTERNAL TABLE IF NOT EXISTS ");

  } else {

    builder.append("CREATE TABLE IF NOT EXISTS ");
  }

  builder.append(tableName);

  // Note the partitioned by columns are deliberately lower case here since Spark does not appear
  // to match columns to Hive partitions if they are not
  builder.append("(descendantSystem STRING, "
      + "descendantValue STRING, "
      + "ancestorSystem STRING,"
      + "ancestorValue String)\n"
      + "PARTITIONED BY (uri STRING, version STRING)\n");

  builder.append("STORED AS PARQUET\n");

  if (location != null) {
    builder.append("LOCATION '")
        .append(location)
        .append("'");
  }

  spark.sql(builder.toString());
}
 

import org.apache.spark.sql.SparkSession; //导入方法依赖的package包/类
/**
 * Creates a table of value records partitioned by valueseturi and valuesetversion.
 *
 * @param spark the spark session
 * @param tableName the name of the values table
 * @param location the location to store the table, or null to create a Hive-managed table
 * @throws IllegalArgumentException if the table name or location are malformed
 */
private static void createValuesTable(SparkSession spark, String tableName, String location) {

  if (!TABLE_NAME_PATTERN.matcher(tableName).matches()) {
    throw new IllegalArgumentException("Invalid table name: " + tableName);
  }

  // Hive will check for well-formed paths, so we just ensure a user isn't attempting to inject
  // additional SQL into the statement
  if (location != null && location.contains(";")) {
    throw new IllegalArgumentException("Invalid path for values table: " + location);
  }

  StringBuilder builder = new StringBuilder();

  if (location != null) {

    builder.append("CREATE EXTERNAL TABLE IF NOT EXISTS ");

  } else {

    builder.append("CREATE TABLE IF NOT EXISTS ");
  }

  builder.append(tableName);

  // Note the partitioned by columns are deliberately lower case here since Spark does not appear
  // to match columns to Hive partitions if they are not
  builder.append("(system STRING, "
      + "version STRING, "
      + "value STRING)\n"
      + "PARTITIONED BY (valueseturi STRING, valuesetversion STRING)\n");

  builder.append("STORED AS PARQUET\n");

  if (location != null) {
    builder.append("LOCATION '")
        .append(location)
        .append("'");
  }

  spark.sql(builder.toString());
}
 

import org.apache.spark.sql.SparkSession; //导入方法依赖的package包/类
/**
 * Creates a table of mapping records partitioned by conceptmapuri and
 * conceptmapversion.
 *
 * @param spark the spark session
 * @param tableName the name of the mapping table
 * @param location the location to store the table, or null to create a Hive-managed table.
 * @throws IllegalArgumentException if the table name or location are malformed.
 */
private static void createMappingTable(SparkSession spark,
    String tableName,
    String location) {

  if (!TABLE_NAME_PATTERN.matcher(tableName).matches()) {
    throw new IllegalArgumentException("Invalid table name: " + tableName);
  }

  // Hive will check for well-formed paths, so we just ensure
  // a user isn't attempting to inject additional SQL into the statement.
  if (location != null && location.contains(";")) {
    throw new IllegalArgumentException("Invalid path for mapping table: "
        + location);
  }

  StringBuilder builder = new StringBuilder();

  if (location != null) {

    builder.append("CREATE EXTERNAL TABLE IF NOT EXISTS ");

  } else {

    builder.append("CREATE TABLE IF NOT EXISTS ");
  }

  builder.append(tableName);

  // Note the partitioned by columns are deliberately lower case here,
  // since Spark does not appear to match columns to
  // Hive partitions if they are not.
  builder.append("(sourceValueSet STRING, "
      + "targetValueSet STRING, "
      + "sourceSystem STRING, "
      + "sourceValue STRING, "
      + "targetSystem STRING, "
      + "targetValue STRING, "
      + "equivalence STRING)\n"
      + "PARTITIONED BY (conceptmapuri STRING, conceptmapversion STRING)\n");

  builder.append("STORED AS PARQUET\n");

  if (location != null) {
    builder.append("LOCATION '")
        .append(location)
        .append("'");
  }

  spark.sql(builder.toString());
}
 

import org.apache.spark.sql.SparkSession; //导入方法依赖的package包/类
public static void main(String[] args) throws StreamingQueryException {
	System.setProperty("hadoop.home.dir", "C:\\softwares\\Winutils");
	SparkSession sparkSession = SparkSession.builder().master("local[*]").appName("structured Streaming Example")
			.config("spark.sql.warehouse.dir", "file:////C:/Users/sgulati/spark-warehouse").getOrCreate();

	Dataset<Row> inStream = sparkSession.readStream().format("socket").option("host", "10.204.136.223")
			.option("port", 9999).load();

	Dataset<FlightDetails> dsFlightDetails = inStream.as(Encoders.STRING()).map(x -> {
		ObjectMapper mapper = new ObjectMapper();
		return mapper.readValue(x, FlightDetails.class);

	}, Encoders.bean(FlightDetails.class));
	
	
	dsFlightDetails.createOrReplaceTempView("flight_details");
	
	Dataset<Row> avdFlightDetails = sparkSession.sql("select flightId, avg(temperature) from flight_details group by flightId");
	
	StreamingQuery query = avdFlightDetails.writeStream()
			  .outputMode("complete")
			  .format("console")
			  .start();

			query.awaitTermination();
	

}
 

import org.apache.spark.sql.SparkSession; //导入方法依赖的package包/类
/**
 * Saves an RDD of bundles as a database, where each table
 * has the resource name. This offers a simple way to load and query
 * bundles in a system, although users with more sophisticated ETL
 * operations may want to explicitly write different entities.
 *
 * <p>
 * Note this will access the given RDD of bundles once per resource name,
 * so consumers with enough memory should consider calling
 * {@link JavaRDD#cache()} so that RDD is not recomputed for each.
 * </p>
 *
 * @param spark the spark session
 * @param bundles an RDD of FHIR Bundles
 * @param database the name of the database to write to
 * @param resourceNames names of resources to be extracted from the bundle and written
 */
public static void saveAsDatabase(SparkSession spark,
    JavaRDD<Bundle> bundles,
    String database,
    String... resourceNames) {

  spark.sql("create database if not exists " + database);

  for (String resourceName : resourceNames) {

    Dataset ds = extractEntry(spark, bundles, resourceName);

    ds.write().saveAsTable(database + "." + resourceName.toLowerCase());
  }
}