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
