@Override public String apply(String input) { return quoteIdentifier(input); }}; }
/** * puts back ticks around components if they look like reserved keywords and joins them with . * @param pathComponents can not contain nulls * @return a dot delimited path * Convert a list of path components to fully qualified dotted schema path * [a,b,c] -> a.b.c * [a,b,c-1] -> a.b.`c-1` * [a,b,c.json] -> a.b.`c.json` */ public static String constructFullPath(Collection<String> pathComponents) { final List<String> quotedPathComponents = Lists.newArrayList(); for (final String component : pathComponents) { checkNotNull(component); quotedPathComponents.add(SqlUtils.quoteIdentifier(component)); } return KEY_JOINER.join(quotedPathComponents); }
private String formatSQLWithSubQuery(String sql, String alias) { if (isStar && orders.isEmpty() && evaledFilters.isEmpty() && groupBys.isEmpty() && joins.isEmpty()) { return sql; } else { if (alias == null) { throw new UnsupportedOperationException("the subquery should be assigned an alias: " + sql); } return formatSQL(evaledCols, orders, "(\n" + indent(sql) + "\n) " + quoteIdentifier(alias), joins, evaledFilters, groupBys); } }
<T> String generateCardGenQuery(String inputColName, String datasetPreviewTable, List<TransformRuleWrapper<T>> evaluators) { StringBuilder queryBuilder = new StringBuilder(); String inputExpr = String.format("%s.%s", quoteIdentifier("dremio_preview_data"), quoteIdentifier(inputColName)); List<String> exprs = Lists.newArrayList(); for(int i=0; i<evaluators.size(); i++) { if (evaluators.get(i).canGenerateExamples()) { final String expr = evaluators.get(i).getExampleFunctionExpr(inputExpr); final String outputColAlias = "example_" + i; exprs.add(String.format("%s AS %s", expr, outputColAlias)); } } exprs.add(String.format("%s AS inputCol", inputExpr)); queryBuilder.append("SELECT\n"); queryBuilder.append(Joiner.on(",\n").join(exprs)); queryBuilder.append(format("\nFROM %s as dremio_preview_data", datasetPreviewTable)); queryBuilder.append(format("\nWHERE %s IS NOT NULL", quoteIdentifier(inputColName))); queryBuilder.append(format("\nLIMIT %d", Card.EXAMPLES_TO_SHOW)); return queryBuilder.toString(); }
<T> String generateMatchCountQuery(String inputColName, String datasetPreviewTable, List<TransformRuleWrapper<T>> evaluators) { StringBuilder queryBuilder = new StringBuilder(); String inputExpr = String.format("%s.%s", quoteIdentifier("dremio_preview_data"), quoteIdentifier(inputColName)); List<String> exprs = Lists.newArrayList(); for(int i=0; i<evaluators.size(); i++) { final String expr = evaluators.get(i).getMatchFunctionExpr(inputExpr); final String outputColAlias = "matched_count_" + i; // Add sum over the true or false expression exprs.add(String.format("sum(CASE WHEN %s THEN 1 ELSE 0 END) AS %s", expr, outputColAlias)); } // Add an count(*) to count the total number of rows in job output. // This was changed for a previous use of sum(1), as this produces null for an empty input set // which we can have if our sample fails all filters and in other cases exprs.add("COUNT(1) as total"); queryBuilder.append("SELECT\n"); queryBuilder.append(Joiner.on(",\n").join(exprs)); queryBuilder.append(format("\nFROM %s as dremio_preview_data", datasetPreviewTable)); return queryBuilder.toString(); } }
@Override public String visit(ExpColumnReference col) throws Exception { String tableAlias = col.getTable(); if (tableAlias == null && isKeyword(col.getName())) { tableAlias = tableName; } if (tableAlias == null) { return quoteIdentifier(col.getName()); } else { return format("%s.%s", quoteIdentifier(tableAlias), quoteIdentifier(col.getName())); } }
@Override public String visit(FromTable name) throws Exception { DatasetPath datasetPath = new DatasetPath(name.getDatasetPath()); List<String> path = new ArrayList<>(); for (String component : datasetPath.toPathList()) { path.add(quoteIdentifier(component)); } String table = Joiner.on(".").join(path) + (name.getAlias() == null ? "" : " AS " + quoteIdentifier(name.getAlias())); return formatSQL(evaledCols, orders, table, joins, evaledFilters, groupBys); }
throw new IllegalArgumentException("Unknown join type " + join.getJoinType().name()); sql.append(join.getRightTable()).append(" AS ").append(quoteIdentifier(joinAlias)).append(" ON "); List<String> conds = new ArrayList<>(); for (JoinCondition c : join.getJoinConditionsList()) { conds.add(format("%s.%s = %s.%s", quoteIdentifier(table), quoteIdentifier(c.getLeftColumn()), quoteIdentifier(joinAlias), quoteIdentifier(c.getRightColumn()) ));
@Override public String getFunctionExpr(String expr, Object... args) { StringBuilder sb = new StringBuilder(); sb.append(expr); for (JsonPathElement e : path) { if (e.isArray()) { sb.append("[").append(e.asArray().getPosition()).append("]"); } else if (e.isObject()) { sb.append(".").append(quoteIdentifier(e.asObject().getField())); } else { throw new IllegalArgumentException("Unknown JSON path element " + e); } } return sb.toString(); }
String quoted = SqlUtils.quoteIdentifier(component); if (!quoted.startsWith(String.valueOf(SqlUtils.QUOTE)) || !quoted.endsWith(String.valueOf(SqlUtils.QUOTE))) { quoted = quoteString(quoted);
/** * Builds the response object for query suggestions. * * @param suggestionList The suggestion list returned from the SqlAdvisor. * * @return The built SuggestionResponse object or null if there are no suggestions. */ public SuggestionResponse buildSuggestionResponse(List<SqlMoniker> suggestionList) { // Return empty response in REST request if (suggestionList == null || suggestionList.isEmpty()) { return null; } // Create and populate suggestion response list List<SuggestionResponse.Suggestion> suggestions = new ArrayList<>(); for (SqlMoniker hint : suggestionList) { // Quote the identifiers if they are not keywords or functions, // and are required to be quoted. List<String> qualifiedNames = hint.getFullyQualifiedNames(); if ((hint.getType() != SqlMonikerType.KEYWORD) && (hint.getType() != SqlMonikerType.FUNCTION)) { qualifiedNames = qualifiedNames.stream().map(name -> quoteIdentifier(name)).collect(Collectors.toList()); } suggestions.add( new SuggestionResponse.Suggestion(Joiner.on(".").join(qualifiedNames),hint.getType().name())); } SuggestionResponse response = new SuggestionResponse(suggestions); return response; }
@Test public void testQuoteIdentifier() { assertEquals("\"window\"", SqlUtils.quoteIdentifier("window")); assertEquals("\"metadata\"", SqlUtils.quoteIdentifier("metadata")); assertEquals("abc", SqlUtils.quoteIdentifier("abc")); assertEquals("abc123", SqlUtils.quoteIdentifier("abc123")); assertEquals("a_bc", SqlUtils.quoteIdentifier("a_bc")); assertEquals("\"a_\"\"bc\"", SqlUtils.quoteIdentifier("a_\"bc")); assertEquals("\"a.\"\"bc\"", SqlUtils.quoteIdentifier("a.\"bc")); assertEquals("\"ab-c\"", SqlUtils.quoteIdentifier("ab-c")); assertEquals("\"ab/c\"", SqlUtils.quoteIdentifier("ab/c")); assertEquals("\"ab.c\"", SqlUtils.quoteIdentifier("ab.c")); assertEquals("\"123\"", SqlUtils.quoteIdentifier("123")); assertEquals("U&\"foo\\000abar\"", SqlUtils.quoteIdentifier("foo\nbar")); }
@Override public String visit(FieldExtractMap extract) throws Exception { if (fieldTransformation.getOperand().getType() != ColumnReference) { throw new IllegalArgumentException("Can only generate extract map for column reference, got " + extract.toString()); } ExpColumnReference col = fieldTransformation.getOperand().getCol(); ExtractMapRule rule = extract.getRule(); String tableAlias = col.getTable() == null ? tableName : col.getTable(); if (tableAlias == null) { throw new IllegalArgumentException("Can only generate extract map for column reference in a table for " + fieldTransformation); } String inputExpr = String.format("%s.%s", quoteIdentifier(tableAlias), quoteIdentifier(col.getName())); return extractMapRecommender.wrapRule(rule).getFunctionExpr(inputExpr); }
@POST @Path("file_preview_unsaved/{path: .*}") @Produces(MediaType.APPLICATION_JSON) @Consumes(MediaType.APPLICATION_JSON) public JobDataFragment previewFormatSettingsStaging(FileFormat fileFormat, @PathParam("path") String path) throws FileNotFoundException, SourceNotFoundException { FilePath filePath = FilePath.fromURLPath(homeName, path); logger.debug("filePath: " + filePath.toPathString()); // use file's location directly to query file String fileLocation = PathUtils.toDottedPath(new org.apache.hadoop.fs.Path(fileFormat.getLocation())); SqlQuery query = new SqlQuery(format("select * from table(%s.%s (%s)) limit 500", SqlUtils.quoteIdentifier(HomeFileSystemStoragePlugin.HOME_PLUGIN_NAME), fileLocation, fileFormat.toTableOptions()), securityContext.getUserPrincipal().getName()); JobUI job = new JobUI(jobsService.submitJob(JobRequest.newBuilder() .setSqlQuery(query) .setQueryType(QueryType.UI_INITIAL_PREVIEW) .build(), NoOpJobStatusListener.INSTANCE)); return job.getData().truncate(500); }
public Map<DataType, Long> getTypeHistogram(final DatasetPath datasetPath, DatasetVersion version, String colName, SqlQuery datasetQuery) { JobUI datasetPreviewJob = DatasetsUtil.getDatasetPreviewJob(executor, datasetQuery, datasetPath, version); String quotedColName = quoteIdentifier(colName); String newSql = format("SELECT typeOf(dremio_values_table.%s) AS dremio_value_type, COUNT(*) as dremio_type_count FROM %s AS dremio_values_table GROUP BY typeOf(dremio_values_table.%s)", quotedColName, datasetPreviewJob.getData().getJobResultsTable(), quotedColName); JobData completeJobData = executor.runQuery(datasetQuery.cloneWithNewSql(newSql), QueryType.UI_INTERNAL_RUN, datasetPath, version).getData(); final Map<DataType, Long> values = new LinkedHashMap<>(); int offset = 0; JobDataFragment data = completeJobData.range(offset, BATCH_SIZE); // Read the values in batches while(data.getReturnedRowCount() > 0) { for (int i = 0; i < data.getReturnedRowCount(); i++) { String typeName = data.extractString("dremio_value_type", i); DataType dataType = DataTypeUtil.getDataType(MinorType.valueOf(typeName)); Long existing = values.get(dataType); if (existing == null) { existing = Long.valueOf(0); } Long newValue = (Long) data.extractValue("dremio_type_count", i); // there are fewer DataTypes than MinorTypes values.put(dataType, existing + newValue); } // Move onto next set of records offset += data.getReturnedRowCount(); data = completeJobData.range(offset, BATCH_SIZE); } return values; }
throws Exception { final String viewName = generateViewName(); String viewFullName = SqlUtils.quoteIdentifier(viewName);
/** * Drop view with given parameters. * * Current schema "dfs_test" * DROP VIEW tmp.viewName * * For the above DROP VIEW query, function parameters values are: * viewSchema = "tmp" * "viewName" = "viewName" * "finalSchema" = "dfs_test.tmp" * * @param viewSchema * @param viewName * @param finalSchema * @throws Exception */ protected void dropViewHelper(final String viewSchema, final String viewName, final String finalSchema) throws Exception{ String viewFullName = SqlUtils.quoteIdentifier(viewName); if (!Strings.isNullOrEmpty(viewSchema)) { viewFullName = viewSchema + "." + viewFullName; } testBuilder() .sqlQuery(String.format("DROP VIEW %s", viewFullName)) .unOrdered() .baselineColumns("ok", "summary") .baselineValues(true, String.format("View [%s.%s] deleted successfully.", finalSchema, viewName)) .go(); }
final String finalSchema, final String viewFields, final String viewDef) throws Exception { String viewFullName = SqlUtils.quoteIdentifier(viewName); if (!Strings.isNullOrEmpty(viewSchema)) { viewFullName = viewSchema + "." + viewFullName;
@Test public void checkUse() throws Exception { // First set the default schema // Then run a query to confirm the content testBuilder() .sqlQuery(format("USE %s", USE_TEST_PATH)) .unOrdered() .baselineColumns("ok", "summary") .baselineValues(true, format("Default schema changed to [%s]", USE_TEST_PATH)) .go(); testBuilder() .sqlQuery(format("SELECT * FROM %s", SqlUtils.quoteIdentifier("foo.json"))) .unOrdered() .baselineColumns("result") .baselineValues("ok") .go(); }
@Test public void checkRelativeUse() throws Exception { // First set the default schema // Set the schema a second time, and verify the returned schema is the correct one // Run a query to confirm the table content testBuilder() .sqlQuery(format("USE %s", USE_TEST_PATH)) .unOrdered() .baselineColumns("ok", "summary") .baselineValues(true, format("Default schema changed to [%s]", USE_TEST_PATH)) .go(); testBuilder() .sqlQuery("USE dir") .unOrdered() .baselineColumns("ok", "summary") .baselineValues(true, format("Default schema changed to [%s.dir]", USE_TEST_PATH)) .go(); testBuilder() .sqlQuery(format("SELECT * FROM %s", SqlUtils.quoteIdentifier("bar.json"))) .unOrdered() .baselineColumns("result") .baselineValues("ok") .go(); }