Thanks Sudhakar ( and David Kewley).
Here is a quick update to add the validation steps and the typo in the
original. Let’s see if there are any other issues with these steps or a simpler
implementation.
0. Verify that the storage plugin and dfs workspaces are the same for the
current and new drill clusters. Note any changes/updates between the 2 drill
clusters.
1. Start sqlline from the user home directory (where the user has write
privileges to the filesystem) and connect to Drill cluster
2. Set the output width to a large number to capture large Views - !set
maxwidth 100000
3. Enable recording of the sqlline output by entering - !record views.sql
4. Run the following SQL query to capture the Views in INFORMATION_SCHEMA -
select concat('create or replace view ', TABLE_SCHEMA,'.',TABLE_NAME,' as ',
VIEW_DEFINITION,';') from INFORMATION_SCHEMA.VIEWS;
5. End recording - !record
6. Exit sqlline - !q
7. Edit the output file views.sql - vi views.sql
— Validate that all the views have been spooled completely - compare counts of
the start of the sql and that the semicolon count at the end
—— grep 'create or replace view' views.sql |wc -l (Outputs number of views
in sql with the create sql)
—— grep ';' views.sql |wc -l (Outputs number of semicolons at the end of the
sql line to verify it spooled the whole view)
— Remove the header lines in the file (typically the first 4 lines) - use dd in
vi
— Remove the last few lines of output that is not SQL (typically 2 lines)
— Replace or remove all the pipe (|) marks in the file — in vi — :%s/|/
(Note to enter a space after the last slash / )
— If required, update any storage plugin/workspace information in the view sql
— Save SQL file — :wq
You now have a sql file with the captured views, which only the required ones
can be extracted, or manipulated as needed.
8. To create the views in Drill simply start sqlline and execute the sql file
— In sqlline — !run views.sql
—Andries
On Jan 28, 2015, at 10:42 AM, Sudhakar Thota <[email protected]> wrote:
> Andries,
>
>
> Please remember to add one more thing.
>
> 1. Save storage plugins
>
> 2. And validating the output file for shortened SQLs if things go beyond
> 10000 lines. Some thing in similar lines as mentioned below. If the numbers
> don’t match increase the width,
>
> Administrators-MacBook-Pro-10:apache-drill-0.6.0-incubating sthota$ grep
> 'create or replace view' views.sql |wc -l
> 22
> Administrators-MacBook-Pro-10:apache-drill-0.6.0-incubating sthota$ grep ';'
> views.sql |wc -l
> 22
>
>
> Thanks
> Sudhakar Thota
>
>
> On Jan 28, 2015, at 9:18 AM, Andries Engelbrecht <[email protected]>
> wrote:
>
>> Is there a function in Drill to recognize the .drill JSON files as views?
>> I.e. copy .drill files from one cluster to another and pick it up vs
>> recreating.
>>
>> I have had issues with previous version upgrades and views, the current
>> upgrade instructions will pretty much also loose the views in the drill
>> cluster itself.
>>
>>
>>
>>
>> On Jan 28, 2015, at 9:10 AM, Jacques Nadeau <[email protected]> wrote:
>>
>>> Can you create a short markdown page for this and we do a pull request to
>>> the gh-pages branch so we can add to the markdown docs?
>>>
>>> Also would be good to note here that all views are actually maintained
>>> independent of Drill. So you can always just copy and move around views as
>>> they are simply JSON files in the DFS. Upgrading shouldn't impact views
>>> since Drill doesn't maintain the metadata.
>>>
>>> On Wed, Jan 28, 2015 at 8:30 AM, Andries Engelbrecht <
>>> [email protected]> wrote:
>>>
>>>> Here are a few simple steps that can be used to export Drill Views & also
>>>> how to import them.
>>>> This can be handy to save views before a major change in the environment,
>>>> also to export views from one Drill cluster and deploy on another.
>>>>
>>>>
>>>> 1. Start sqlline from the user home directory (where the user has write
>>>> privileges to the filesystem) and connect to Drill cluster
>>>>
>>>> 2. Set the output width to a large number to capture large Views - !set
>>>> maxwidth 100000
>>>>
>>>> 3. Enable recording of the sqlline output by entering - !record views.sql
>>>>
>>>> 4. Run the following SQL query to capture the Views in INFORMATION_SCHEMA -
>>>> select concat('create or replace view ', TABLE_SCHEMA,'.',TABLE_NAME,' as
>>>> ', VIEW_DEFINITION,’;') from INFORMATION_SCHEMA.VIEWS;
>>>>
>>>> 5. End recording - !record
>>>>
>>>> 6. Exit sqlline - !q
>>>>
>>>> 7. Edit the output file views.sql - vi views.sql
>>>> — Remove the header lines in the file (typically the first 4 lines) - use
>>>> dd in vi
>>>> — Remove the last few lines of output that is not SQL (typically 2 lines)
>>>> — Replace or remove all the pipe (|) marks in the file — in vi — :%s/|/
>>>> (Note to enter a space after the last slash / )
>>>> — Save SQL file — :wq
>>>>
>>>> You now have a sql file with the captured views, which only the required
>>>> ones can be extracted, or manipulated as needed.
>>>>
>>>> 8. To create the views in Drill simply start sqlline and execute the sql
>>>> file
>>>> — In sqlline — !run views.sql
>>>>
>>>>
>>>> Hope this helps a few people as they build up a body of work in Drill.
>>>>
>>>> —Andries
>>>>
>>>>
>>
>
