@Test public void testAlterOption() throws Exception { SqlQuery ctas = getQueryFromSQL("alter session set \"planner.enable_multiphase_agg\"=true"); Job ctasJob = jobsService.submitJob(JobRequest.newBuilder() .setSqlQuery(ctas) .build(), NoOpJobStatusListener.INSTANCE); ctasJob.getData().loadIfNecessary(); }
@Test public void testExplain() throws Exception { SqlQuery ctas = getQueryFromSQL("EXPLAIN PLAN FOR SELECT * FROM sys.version"); Job ctasJob = jobsService.submitJob(JobRequest.newBuilder() .setSqlQuery(ctas) .build(), NoOpJobStatusListener.INSTANCE); ctasJob.getData().loadIfNecessary(); }
@Test public void testAliasedQuery() throws Exception { SqlQuery ctas = getQueryFromSQL("SHOW SCHEMAS"); Job ctasJob = jobsService.submitJob(JobRequest.newBuilder() .setSqlQuery(ctas) .build(), NoOpJobStatusListener.INSTANCE); ctasJob.getData().loadIfNecessary(); }
@Test public void testVirtualDatasetWithNotNullFields() throws Exception { final LocalJobsService jobsService = (LocalJobsService) l(JobsService.class); expectSuccess(getBuilder(getAPIv2().path("space/space1")).buildPut(Entity.json(new Space(null, "space1", null, null, null, 0, null))), Space.class); final String pathName = "space1.v1"; final DatasetPath numbersJsonPath = new DatasetPath(pathName); DatasetUI numbersJsonVD = createDatasetFromSQLAndSave(numbersJsonPath, "select row_number() over (order by a) as rnk, a from cp.\"json/numbers.json\"", asList("cp")); final SqlQuery query = getQueryFromSQL(String.format("select t1.rnk, t1.a from %s t1 join %s t2 on t1.rnk = t2.rnk+1", pathName, pathName)); final Job ctasJob = jobsService.submitJob(JobRequest.newBuilder() .setSqlQuery(query) .build(), NoOpJobStatusListener.INSTANCE); ctasJob.getData().loadIfNecessary(); }
@Test public void testVirtualDatasetWithTimestampDiff() throws Exception { final LocalJobsService jobsService = (LocalJobsService) l(JobsService.class); expectSuccess(getBuilder(getAPIv2().path("space/space1")).buildPut(Entity.json(new Space(null, "space1", null, null, null, 0, null))), Space.class); final String pathName = "space1.v1"; final DatasetPath datetimePath = new DatasetPath(pathName); DatasetUI dateTimeVD = createDatasetFromSQLAndSave(datetimePath, "select timestampdiff(SECOND, datetime1, datetime2) as tsdiff from cp.\"json/datetime.json\"", asList("cp")); final SqlQuery query = getQueryFromSQL(String.format("select * from %s", pathName, pathName)); final Job ctasJob = jobsService.submitJob(JobRequest.newBuilder() .setSqlQuery(query) .build(), NoOpJobStatusListener.INSTANCE); ctasJob.getData().loadIfNecessary(); }
@Test public void testVirtualDatasetWithChar() throws Exception { final LocalJobsService jobsService = (LocalJobsService) l(JobsService.class); expectSuccess(getBuilder(getAPIv2().path("space/space1")).buildPut(Entity.json(new Space(null,"space1", null, null, null, 0, null))), Space.class); final String pathName = "space1.v1"; final DatasetPath numbersJsonPath = new DatasetPath(pathName); DatasetUI numbersJsonVD = createDatasetFromSQLAndSave(numbersJsonPath, "select CASE WHEN a > 2 THEN 'less than 2' ELSE 'greater than 2' END as twoInfo, a from cp.\"json/numbers.json\"", asList("cp")); final SqlQuery query = getQueryFromSQL(String.format("select * from %s", pathName)); final Job ctasJob = jobsService.submitJob(JobRequest.newBuilder() .setSqlQuery(query) .build(), NoOpJobStatusListener.INSTANCE); ctasJob.getData().loadIfNecessary(); final SqlQuery query2 = getQueryFromSQL(String.format("select count(*) from %s where a > 2", pathName)); final Job ctasJob2 = jobsService.submitJob(JobRequest.newBuilder() .setSqlQuery(query) .build(), NoOpJobStatusListener.INSTANCE); ctasJob2.getData().loadIfNecessary(); }
@Test public void testReflectionsTable() { jobsService.submitJob(JobRequest.newBuilder() .setSqlQuery(getQueryFromSQL("SELECT * FROM sys.reflections")) .setDatasetPath(NONE_PATH) .build(), NoOpJobStatusListener.INSTANCE).getData().loadIfNecessary(); }
@Test public void testMaterializationsTable() { jobsService.submitJob(JobRequest.newBuilder() .setSqlQuery(getQueryFromSQL("SELECT * FROM sys.materializations")) .setDatasetPath(NONE_PATH) .build(), NoOpJobStatusListener.INSTANCE).getData().loadIfNecessary(); } }
@Test public void testCTASAndDropTable() throws Exception { // Create a table SqlQuery ctas = getQueryFromSQL("CREATE TABLE \"$scratch\".\"ctas\" AS select * from cp.\"json/users.json\" LIMIT 1"); Job ctasJob = jobsService.submitJob(JobRequest.newBuilder() .setSqlQuery(ctas) .setQueryType(QueryType.UI_RUN) .build(), NoOpJobStatusListener.INSTANCE); ctasJob.getData().loadIfNecessary(); FileSystemPlugin plugin = (FileSystemPlugin) getCurrentDremioDaemon().getBindingProvider().lookup(CatalogService.class).getSource("$scratch"); // Make sure the table data files exist File ctasTableDir = new File(plugin.getConfig().getPath().toString(), "ctas"); assertTrue(ctasTableDir.exists()); assertTrue(ctasTableDir.list().length >= 1); // Now drop the table SqlQuery dropTable = getQueryFromSQL("DROP TABLE \"$scratch\".\"ctas\""); Job dropTableJob = jobsService.submitJob(JobRequest.newBuilder() .setSqlQuery(dropTable) .setQueryType(QueryType.ACCELERATOR_DROP) .build(), NoOpJobStatusListener.INSTANCE); dropTableJob.getData().loadIfNecessary(); // Make sure the table data directory is deleted assertFalse(ctasTableDir.exists()); }
@Test public void testCTASReplace() throws Exception { NamespaceKey namespaceKey = new NamespaceKey("ctasSpace"); SpaceConfig spaceConfig = new SpaceConfig(); spaceConfig.setName("ctasSpace"); newNamespaceService().addOrUpdateSpace(namespaceKey, spaceConfig); SqlQuery ctas = getQueryFromSQL("CREATE OR REPLACE VIEW ctasSpace.ctastest AS select * from (VALUES (1))"); Job ctasJob = jobsService.submitJob(JobRequest.newBuilder() .setSqlQuery(ctas) .setQueryType(QueryType.UI_RUN) .build(), NoOpJobStatusListener.INSTANCE); waitForCompletion(ctasJob); ctas = getQueryFromSQL("CREATE OR REPLACE VIEW ctasSpace.ctastest AS select * from (VALUES (2))"); ctasJob = jobsService.submitJob(JobRequest.newBuilder() .setSqlQuery(ctas) .setQueryType(QueryType.UI_RUN) .build(), NoOpJobStatusListener.INSTANCE); waitForCompletion(ctasJob); newNamespaceService().deleteSpace(namespaceKey, newNamespaceService().getSpace(namespaceKey).getTag()); }
Job job1_0 = jobsService.submitJob(JobRequest.newBuilder() .setSqlQuery(getQueryFromSQL("select * from LocalFS1.\"dac-sample1.json\" limit 1")) .setDatasetPath(ds1.toNamespaceKey()) .setDatasetVersion(new DatasetVersion("v1")).build(), NoOpJobStatusListener.INSTANCE); Job job2_0 = jobsService.submitJob(JobRequest.newBuilder() .setSqlQuery(getQueryFromSQL("select * from LocalFS1.\"dac-sample1.json\" limit 1")) .setDatasetPath(ds2.toNamespaceKey()) .setDatasetVersion(new DatasetVersion("v1")).build(), NoOpJobStatusListener.INSTANCE); Job job3_0 = jobsService.submitJob(JobRequest.newBuilder() .setSqlQuery(getQueryFromSQL("select * from LocalFS1.\"dac-sample1.json\" limit 1")) .setDatasetPath(ds3.toNamespaceKey()) Job job1_2 = jobsService.submitJob(JobRequest.newBuilder() .setSqlQuery(getQueryFromSQL("select * from LocalFS1.\"dac-sample1.json\" limit 1")) .setDatasetPath(ds1.toNamespaceKey()) .setDatasetVersion(new DatasetVersion("v1")).build(), NoOpJobStatusListener.INSTANCE); Job job1_3 = jobsService.submitJob(JobRequest.newBuilder() .setSqlQuery(getQueryFromSQL("select * from LocalFS1.\"dac-sample1.json\" limit 1")) .setDatasetPath(ds1.toNamespaceKey()) .setDatasetVersion(new DatasetVersion("v2")).build(), NoOpJobStatusListener.INSTANCE); Job job2_2 = jobsService.submitJob(JobRequest.newBuilder() .setSqlQuery(getQueryFromSQL("select * from LocalFS1.\"dac-sample1.json\" limit 1")) .setDatasetPath(ds2.toNamespaceKey()) Job job1_4 = jobsService.submitJob(JobRequest.newBuilder() .setSqlQuery(getQueryFromSQL("select * from LocalFS1.\"dac-sample1.json\" limit 1")) .setDatasetPath(ds1.toNamespaceKey())
@Test public void testJobCleanup() throws Exception { jobsService = (LocalJobsService) l(JobsService.class); SqlQuery ctas = getQueryFromSQL("SHOW SCHEMAS"); Job job = jobsService.submitJob(JobRequest.newBuilder() .setSqlQuery(ctas) .build(), NoOpJobStatusListener.INSTANCE); job.getData().loadIfNecessary(); SabotContext context = l(SabotContext.class); OptionValue days = OptionValue.createLong(OptionType.SYSTEM, ExecConstants.RESULTS_MAX_AGE_IN_DAYS.getOptionName(), 0); context.getOptionManager().setOption(days); OptionValue millis = OptionValue.createLong(OptionType.SYSTEM, ExecConstants.DEBUG_RESULTS_MAX_AGE_IN_MILLISECONDS.getOptionName(), 10); context.getOptionManager().setOption(millis); Thread.sleep(20); LocalJobsService.CleanupTask cleanupTask = jobsService.new CleanupTask(); cleanupTask.cleanup(); //make sure that the job output directory is gone assertFalse(jobsService.getJobResultsStore().jobOutputDirectoryExists(job.getJobId())); job = jobsService.getJob(job.getJobId()); assertFalse(JobDetailsUI.of(job).getResultsAvailable()); context.getOptionManager().setOption(OptionValue.createLong(OptionType.SYSTEM, ExecConstants.RESULTS_MAX_AGE_IN_DAYS.getOptionName(), 30)); context.getOptionManager().setOption(OptionValue.createLong(OptionType.SYSTEM, ExecConstants.DEBUG_RESULTS_MAX_AGE_IN_MILLISECONDS.getOptionName(), 0)); }