Automated maintenance of external tables in database systems
Systems, methods, and devices for automated maintenance of external tables in database systems are disclosed. A method includes receiving, by a database platform, read access to content in an external data storage platform that is separate from the database platform. The method includes defining an external table based on the content in the external data storage platform. The method includes connecting the database platform to the external table such that the database platform has read access for the external table and does not have write access for the external table. The method includes generating metadata for the external table, the metadata comprising information about data stored in the external table. The method includes receiving a notification that a modification has been made to the content in the external data storage platform, the modification comprising one or more of an addition of a file, a deletion of a file, or an update to a file in a source location for the external table. The method includes refreshing the metadata for the external table in response to the modification being made to the content in the external data storage platform.
1. A method performed by a database platform executing instructions on at least one hardware processor, the method comprising:
receiving a query that is directed at least in part to external data in an external table, the external table being stored on an external data storage platform that is external to the database platform and to which the database platform does not have write access, the external table being partitioned into a plurality of partitions according to a hierarchical structure of a source directory in which the external data is stored on the external data storage platform, the hierarchical structure of the source directory comprising a hierarchy of folders in the source directory, the folders corresponding to the partitions in the plurality of partitions, the folders comprising files containing the external data of the external table;
identifying, from external-table metadata that identifies the plurality of partitions, a subset of the plurality of partitions as comprising data that satisfies the query, the external-table metadata being stored in a data storage platform of the database platform; and
responding to the query at least in part with the data that satisfies the query from one or more folders in the hierarchy of folders, the one or more folders corresponding to the subset of the plurality of partitions.
2. The method of claim 1 , further comprising:
receiving hierarchy information defining the hierarchical structure of the source directory in which the external data is stored; and
generating the external-table metadata based at least in part on the received hierarchy information.
3. The method of claim 1 , further comprising:
receiving, from the external data storage platform, a notification of a change having been made to the external table, the notification indicating a particular folder in the hierarchical structure in which the change was made; and
updating the external-table metadata to reflect the change having been made in the particular folder in the hierarchical structure.
4. The method of claim 3 , wherein the change comprises one or more of a file having been added to the particular folder, a file having been modified in the particular folder, and a file having been deleted from the particular folder.
5. The method of claim 3 , further comprising:
generating a materialized view over the external table;
storing the materialized view in the data storage platform of the database platform; and
updating the stored materialized view to reflect the change having been made to the external table.
6. The method of claim 5 , wherein the materialized view comprises a copy of the external table.
7. The method of claim 5 , wherein the materialized view comprises a result of a previous query.
8. The method of claim 5 , further comprising responding to a second query at least in part with reference to the stored materialized view.
9. The method of claim 1 , wherein the external-table metadata is stored in a partition storage object in the data storage platform of the database platform.
10. The method of claim 1 , further comprising refreshing the external-table metadata in response to a threshold number of changes being made to the external data in the external table.
11. A database platform comprising:
at least one processor; and
one or more non-transitory computer readable storage media containing instructions that, when executed by the at least one processor, cause the database platform to perform operations comprising:
receiving a query that is directed at least in part to external data in an external table, the external table being stored on an external data storage platform that is external to the database platform and to which the database platform does not have write access, the external table being partitioned into a plurality of partitions according to a hierarchical structure of a source directory in which the external data is stored on the external data storage platform, the hierarchical structure of the source directory comprising a hierarchy of folders in the source directory, the folders corresponding to the partitions in the plurality of partitions, the folders comprising files containing the external data of the external table;
identifying, from external-table metadata that identifies the plurality of partitions, a subset of the plurality of partitions as comprising data that satisfies the query, the external-table metadata being stored in a data storage platform of the database platform; and
responding to the query at least in part with the data that satisfies the query from one or more folders in the hierarchy of folders, the one or more folders corresponding to the subset of the plurality of partitions.
12. The database platform of claim 11 , the operations further comprising:
receiving hierarchy information defining the hierarchical structure of the source directory in which the external data is stored; and
generating the external-table metadata based at least in part on the received hierarchy information.
13. The database platform of claim 11 , the operations further comprising:
receiving, from the external data storage platform, a notification of a change having been made to the external table, the notification indicating a particular folder in the hierarchical structure in which the change was made; and
updating the external-table metadata to reflect the change having been made in the particular folder in the hierarchical structure.
14. The database platform of claim 13 , wherein the change comprises one or more of a file having been added to the particular folder, a file having been modified in the particular folder, and a file having been deleted from the particular folder.
15. The database platform of claim 13 , the operations further comprising:
generating a materialized view over the external table;
storing the materialized view in the data storage platform of the database platform; and
updating the stored materialized view to reflect the change having been made to the external table.
16. The database platform of claim 15 , wherein the materialized view comprises a copy of the external table.
17. The database platform of claim 15 , wherein the materialized view comprises a result of a previous query.
18. The database platform of claim 15 , the operations further comprising responding to a second query at least in part with reference to the stored materialized view.
19. The database platform of claim 11 , wherein the external-table metadata is stored in a partition storage object in the data storage platform of the database platform.
20. The database platform of claim 11 , the operations further comprising refreshing the external-table metadata in response to a threshold number of changes being made to the external data in the external table.
21. One or more non-transitory computer readable storage media containing instructions that, when executed by at least one hardware processor of a database platform, cause the database platform to perform operations comprising:
receiving a query that is directed at least in part to external data in an external table, the external table being stored on an external data storage platform that is external to the database platform and to which the database platform does not have write access, the external table being partitioned into a plurality of partitions according to a hierarchical structure of a source directory in which the external data is stored on the external data storage platform, the hierarchical structure of the source directory comprising a hierarchy of folders in the source directory, the folders corresponding to the partitions in the plurality of partitions, the folders comprising files containing the external data of the external table;
identifying, from external-table metadata that identifies the plurality of partitions, a subset of the plurality of partitions as comprising data that satisfies the query, the external-table metadata being stored in a data storage platform of the database platform; and
responding to the query at least in part with the data that satisfies the query from one or more folders in the hierarchy of folders, the one or more folders corresponding to the subset of the plurality of partitions.
22. The one or more non-transitory computer readable storage media of claim 21 , the operations further comprising:
receiving hierarchy information defining the hierarchical structure of the source directory in which the external data is stored; and
generating the external-table metadata based at least in part on the received hierarchy information.
23. The one or more non-transitory computer readable storage media of claim 21 , the operations further comprising:
receiving, from the external data storage platform, a notification of a change having been made to the external table, the notification indicating a particular folder in the hierarchical structure in which the change was made; and
updating the external-table metadata to reflect the change having been made in the particular folder in the hierarchical structure.
24. The one or more non-transitory computer readable storage media of claim 21 , the operations further comprising:
generating a materialized view over the external table, wherein the materialized view comprises a copy of the external table;
storing the materialized view in the data storage platform of the database platform; and
responding to a second query at least in part with reference to the stored materialized view.