How to store/export personal settings of Drill

["benj.dev" <be...@laposte.net.INVALID>]

Hi,

I would like to know if it's possible to configure options at Drill startup
I know that it's possible to do ALTER SESSION/SYSTEM in command line and
if it's SYSTEM the value will be retained even after a reboot.
I can use the webinterface to change the value of the options
(ip:8047/options)
In the documentation it's seems to be possible to do drillbit.sh start
-Dname=value but I'm not sure it's work for all configuration options
(For example, I couldn't make it work for store.parquet.use_new_reader)
- maybe It's only work for listed Configuring Start-Up Options in
https://drill.apache.org/docs/start-up-options/

But I would like to have a config file (maybe already exists) with
options to (re)define/ensure at startup of a drillbit.
I need this file to export/exchange the configuration in different
cluster/situations
I don't know if there is a central file to keep these value or if it's
possible to use drill-override.conf. I have try for
"store.parquet.use_new_reader" without any effect (syntax problem ? or
not possible to do that ?)

If this is not possible, how do you explicit the default options that
you choose for the configuration ?

And in additional reflection is it normal for explicitly defined options
in a session to be overwritten by a SYSTEM SET in another SESSION.
Is it a bug ? Personally, I would have hoped that any option defined in
a session would retain its value until the end of the session and can't
be impacted by SYSTEM change in the middle of the session.
More, I think that at session start, we should have a snapshot of the
options defined in SYSTEM and the outside shouldn't have the possibility
to modify that.

Thank for any opinion and explanation.

Re: How to store/export personal settings of Drill

[Kunal Khatua <ku...@apache.org>]

That's very simple! You can query the table - "sys.options" and use CTAS command.

The simplest approach would be the following steps:
1. Log into a SQLLine session
2. Tinker with all the settings that you are trying, using "ALTER SESSION". This ensures that nothing permanently persists.
3. Run the following to define the output format that you'll save in, as JSON (this will be more useful than CSV, which is a more compact format).
    ALTER SESSION SET `store.format`='json';
4. Run the following to pick a *writable* workspace. For me, it is `dfs.root`
    USE dfs.root;
5. Run the following to capture only the changed values:
    CREATE TABLE favoriteOptions AS
      SELECT * FROM sys.options
      WHERE status <> 'DEFAULT';
6. Navigate on your file system to that directory and you'll find the favoriteOptions dir with a JSON file: 
{
  "name" : "exec.hashjoin.bloom_filter.max.size",
  "kind" : "BIGINT",
  "accessibleScopes" : "ALL",
  "val" : "8388608",
  "status" : "CHANGED",
  "optionScope" : "SYSTEM",
  "description" : ""
} {
  "name" : "store.format",
  "kind" : "VARCHAR",
  "accessibleScopes" : "ALL",
  "val" : "json",
  "status" : "CHANGED",
  "optionScope" : "SESSION",
  "description" : "Output format for data written to tables with the CREATE TABLE AS (CTAS) command. Allowed values are parque..."
}


The reason I suggested JSON is because now, you can even specify which columns you want in this 'backup' and only change those entries in your drill-override.conf file before restarting the Drillbits with your 'optimal' config.

Hope that helps

~ KK

On 4/11/2019 9:47:51 AM, benj.dev <be...@laposte.net.invalid> wrote:
Hi,

I would like to know if it's possible to configure options at Drill startup
I know that it's possible to do ALTER SESSION/SYSTEM in command line and
if it's SYSTEM the value will be retained even after a reboot.
I can use the webinterface to change the value of the options
(ip:8047/options)
In the documentation it's seems to be possible to do drillbit.sh start
-Dname=value but I'm not sure it's work for all configuration options
(For example, I couldn't make it work for store.parquet.use_new_reader)
- maybe It's only work for listed Configuring Start-Up Options in
https://drill.apache.org/docs/start-up-options/

But I would like to have a config file (maybe already exists) with
options to (re)define/ensure at startup of a drillbit.
I need this file to export/exchange the configuration in different
cluster/situations
I don't know if there is a central file to keep these value or if it's
possible to use drill-override.conf. I have try for
"store.parquet.use_new_reader" without any effect (syntax problem ? or
not possible to do that ?)

If this is not possible, how do you explicit the default options that
you choose for the configuration ?

And in additional reflection is it normal for explicitly defined options
in a session to be overwritten by a SYSTEM SET in another SESSION.
Is it a bug ? Personally, I would have hoped that any option defined in
a session would retain its value until the end of the session and can't
be impacted by SYSTEM change in the middle of the session.
More, I think that at session start, we should have a snapshot of the
options defined in SYSTEM and the outside shouldn't have the possibility
to modify that.

Thank for any opinion and explanation.