@Override public SqlResult sql(String command) throws DDFException { return this.sql(command, (Integer) null); }
@Override public SqlTypedResult sqlTyped(String command, Integer maxRows, DataSourceDescriptor dataSource) throws DDFException { // @Note This is another possible solution, which I think is more stable. // return this.getDummyDDF().getSqlHandler().sqlTyped(command, maxRows, dataSource); return new SqlTypedResult(sql(command, maxRows, dataSource)); }
public static void main(String[] args) throws DDFException { DDFManager manager = DDFManager.get(DDFManager.EngineType.SPARK); manager.sql("drop table if exists airline", false); manager.sql("create table airline (Year int,Month int,DayofMonth int," + "DayOfWeek int,DepTime int,CRSDepTime int,ArrTime int," + "CRSArrTime int,UniqueCarrier string, FlightNum int, " + "TailNum string, ActualElapsedTime int, CRSElapsedTime int, " + "AirTime int, ArrDelay int, DepDelay int, Origin string, " + "Dest string, Distance int, TaxiIn int, TaxiOut int, Cancelled int, " + "CancellationCode string, Diverted string, CarrierDelay int, " + "WeatherDelay int, NASDelay int, SecurityDelay int, LateAircraftDelay int ) " + "ROW FORMAT DELIMITED FIELDS TERMINATED BY ','", false); manager.sql("load data local inpath 'resources/test/airline.csv' into table airline", false); DDF ddf = manager.sql2ddf("SELECT * FROM AIRLINE", false); long nrow = ddf.getNumRows(); int ncol = ddf.getNumColumns(); System.out.println("Number of data row is " + nrow); System.out.println("Number of data columns is " + ncol); } }
+ serdesString + " STORED AS TEXTFILE LOCATION '"+ uri.toString() + "'"; this.mDDFManager.sql(sqlCmd, false); DDF ddf = this.mDDFManager.sql2ddf(String.format("select * from %s", hiveTableName), false);
@Override public SqlResult sql(String command, Integer maxRows) throws DDFException { return this.sql(command, maxRows, null); }
/** * @brief Test ordinary spark query. * @throws DDFException */ @Test public void testLoading() throws DDFException { SQLDataSourceDescriptor sqlDataSourceDescriptor = new SQLDataSourceDescriptor(null, false, null, null, null); manager.sql("drop table if exists airline", sqlDataSourceDescriptor); manager.sql("create table airline (Year int,Month int,DayofMonth int," + "DayOfWeek int,DepTime int,CRSDepTime int,ArrTime int," + "CRSArrTime int,UniqueCarrier string, FlightNum int, " + "TailNum string, ActualElapsedTime int, CRSElapsedTime int, " + "AirTime int, ArrDelay int, DepDelay int, Origin string, " + "Dest string, Distance int, TaxiIn int, TaxiOut int, Cancelled int, " + "CancellationCode string, Diverted string, CarrierDelay int, " + "WeatherDelay int, NASDelay int, SecurityDelay int, LateAircraftDelay int ) " + "ROW FORMAT DELIMITED FIELDS TERMINATED BY ','", sqlDataSourceDescriptor); manager.sql("load data local inpath '../resources/test/airline.csv' " + "into table airline", sqlDataSourceDescriptor); DDF ddf = manager.sql2ddf("select year, month, dayofweek, deptime, arrtime,origin, distance, arrdelay, " + "depdelay, carrierdelay, weatherdelay, nasdelay, securitydelay, lateaircraftdelay from airline", sqlDataSourceDescriptor); this.manager.setDDFName(ddf, "airlineDDF"); // DDF sql2ddfRet = manager.sql2ddf("select * from " + // "ddf://adatao/airlineDDF"); }
public void createTableMovie() throws DDFException { manager.sql("drop table if exists movie", false); manager.sql("create table movie (" + "idx string,title string,year int,length int,budget double,rating double,votes int," + "r1 double,r2 double,r3 double,r4 double,r5 double,r6 double,r7 double,r8 double,r9 double,r10 double," + "mpaa string,Action int,Animation int,Comedy int,Drama int,Documentary int,Romance int,Short int" + ") ROW FORMAT DELIMITED FIELDS TERMINATED BY ','", false); manager.sql("load data local inpath '../resources/test/movies.csv' into table movie", false); }
@Test public void testSimpleSparkDDFManager() throws DDFException { createTableAirline(); List<String> l = manager.sql("select * from airline", false).getRows(); Assert.assertEquals(31, l.size()); List<String> v = manager.sql("select count(*) from airline", false).getRows(); Assert.assertEquals(1, v.size()); Assert.assertEquals("31", v.get(0)); DDF ddf = manager.sql2ddf("select year, month, dayofweek, deptime, arrtime,origin, distance, arrdelay, " + "depdelay, carrierdelay, weatherdelay, nasdelay, securitydelay, lateaircraftdelay from airline", false); Assert.assertEquals(14, ddf.getSummary().length); manager.setDDFName(ddf, "myddf"); manager.addDDF(ddf); Assert.assertEquals(ddf, manager.getDDF(ddf.getUUID())); } }
public void createTableCarOwner() throws DDFException { manager.sql("drop table if exists carowner", false); manager.sql("CREATE TABLE carowner (" + "name string, cyl int, disp double" + ") ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '", false); manager.sql("load data local inpath '../resources/test/carowner' into table carowner", false); }
public void createTableRatings() throws DDFException { manager.sql("drop table if exists ratings", false); manager.sql("create table ratings (userid int,movieid int,score double ) " + "ROW FORMAT DELIMITED FIELDS TERMINATED BY ','", false); manager.sql("load data local inpath '../resources/test/ratings.data' into table ratings", false); }
public void createTableSmiths2() throws DDFException { manager.sql("drop table if exists smiths2", false); manager.sql("create table smiths2 (subject string, variable string, value double) " + "ROW FORMAT DELIMITED FIELDS TERMINATED BY ','", false); manager.sql("load data local inpath '../resources/test/smiths2' into table smiths2", false); }
public void createTableMtcars() throws DDFException { manager.sql("drop table if exists mtcars", false); manager.sql("CREATE TABLE mtcars (" + "mpg double, cyl int, disp double, hp int, drat double, wt double, qsec double, vs int, am int, gear int, carb int" + ") ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '", false); manager.sql("load data local inpath '../resources/test/mtcars' into table mtcars", false); }
public SqlResult sql(String command, Boolean queryOnDDF) throws DDFException { return this.sql(command, new SQLDataSourceDescriptor(null, queryOnDDF,null, null, null)); }
public void createTableAirline() throws DDFException { manager.sql("drop table if exists airline", false); manager.sql("create table airline (Year int,Month int,DayofMonth int," + "DayOfWeek int,DepTime int,CRSDepTime int,ArrTime int," + "CRSArrTime int,UniqueCarrier string, FlightNum int, " + "TailNum string, ActualElapsedTime int, CRSElapsedTime int, " + "AirTime int, ArrDelay int, DepDelay int, Origin string, " + "Dest string, Distance int, TaxiIn int, TaxiOut int, Cancelled int, " + "CancellationCode string, Diverted string, CarrierDelay int, " + "WeatherDelay int, NASDelay int, SecurityDelay int, LateAircraftDelay int ) " + "ROW FORMAT DELIMITED FIELDS TERMINATED BY ','", false); manager.sql("load data local inpath '../resources/test/airline.csv' into table airline", false); }
public void createTableAirlineWithNA() throws DDFException { manager.sql("drop table if exists airline", false); manager.sql("create table airline (Year int,Month int,DayofMonth int," + "DayOfWeek int,DepTime int,CRSDepTime int,ArrTime int," + "CRSArrTime int,UniqueCarrier string, FlightNum int, " + "TailNum string, ActualElapsedTime int, CRSElapsedTime int, " + "AirTime int, ArrDelay int, DepDelay int, Origin string, " + "Dest string, Distance int, TaxiIn int, TaxiOut int, Cancelled int, " + "CancellationCode string, Diverted string, CarrierDelay int, " + "WeatherDelay int, NASDelay int, SecurityDelay int, LateAircraftDelay int ) " + "ROW FORMAT DELIMITED FIELDS TERMINATED BY ','", false); manager.sql("load data local inpath '../resources/test/airlineWithNA.csv' into table airline", false); }
@Override public double aggregateOnColumn(AggregateFunction function, String column) throws DDFException { return Double.parseDouble(this.getManager() .sql(String.format("SELECT %s from %s", function.toString(column), this.getDDF().getTableName()), false).getRows ().get(0)); }
protected long getNumRowsImpl() throws DDFException { this.mLog.debug("get NumRows Impl called"); try { String sqlcmd = "SELECT COUNT(*) FROM {1}"; List<String> rs = this.getManager().sql(sqlcmd, new SQLDataSourceDescriptor(sqlcmd, null, null, null, this .getDDF().getUUID().toString())).getRows(); return Long.parseLong(rs.get(0)); } catch (Exception e) { throw new DDFException("Error getting NRow", e); } }
public SqlResult sql(String sqlCommand, String errorMessage) throws DDFException { try { // sqlCommand = sqlCommand.replace("@this", this.getTableName()); // TODO: what is format? // return this.getManager().sql(String.format(sqlCommand, this.getTableName())); sqlCommand = sqlCommand.replace("@this", "{1}"); sqlCommand = String.format(sqlCommand, "{1}"); SQLDataSourceDescriptor sqlDS = new SQLDataSourceDescriptor(sqlCommand, null, null,null, this.getUUID().toString()); return this.getManager().sql(sqlCommand, null, sqlDS); } catch (Exception e) { throw new DDFException(String.format(errorMessage, this.getTableName()), e); } }
/** * Performs the equivalent of a SQL aggregation statement like "SELECT year, month, AVG(depdelay), MIN(arrdelay) FROM * airline GROUP BY year, month" * * @param fields {@link AggregateField}s representing a list of column specs, some of which may be aggregated, while other * non-aggregated fields are the GROUP BY keys * @return * @throws DDFException */ @Override public AggregationResult aggregate(List<AggregateField> fields) throws DDFException { String tableName = this.getDDF().getTableName(); String sqlCmd = AggregateField.toSql(fields, tableName); mLog.info("SQL Command: " + sqlCmd); int numUnaggregatedFields = 0; for (AggregateField field : fields) { if (!field.isAggregated()) numUnaggregatedFields++; } try { List<String> result = this.getManager().sql(sqlCmd, false).getRows(); return AggregationResult.newInstance(result, numUnaggregatedFields); } catch (Exception e) { e.printStackTrace(); mLog.error(e.getMessage()); throw new DDFException("Unable to query from " + tableName, e); } }
@Override public double computeCorrelation(String columnA, String columnB) throws DDFException { if (!(this.getDDF().getColumn(columnA).isNumeric() || this.getDDF().getColumn(columnB).isNumeric())) { throw new DDFException("Only numeric fields are accepted!"); } String sqlCmd = String.format("SELECT CORR(%s, %s) FROM %s", columnA, columnB, this.getDDF().getTableName()); try { List<String> rs = this.getManager().sql(sqlCmd, false).getRows(); return Utils.roundUp(Double.parseDouble(rs.get(0))); } catch (Exception e) { throw new DDFException(String.format("Unable to get CORR(%s, %s) FROM %s", columnA, columnB, this.getDDF() .getTableName()), e); } }