| Configuration Item | Description |
| Host | The hostname or IP address where your RDBMS server exists |
| Port | The port used for communicating with the RDBMS |
| Service name | The SID to register into Dremio |
| Username & Password | The access credentials granted privilege to the tables to be queries. It is ideal to have this id as a service account |
Now take the information you gathered about your Oracle instance (or whichever RDBMS you are offloading) and enter that information into the General options section of the edit Source screen.
The last step is to set the “record fetch size”. The larger the “Record fetch size” the more efficient the record reads from Oracle to Dremio. But be careful as the setting increases, the performance benefits can reduce. Work with your Oracle system administrator with help to assign this value to the highest possible value. One thing to keep in mind when determining the value, as you offload queries from your RDBMS more resources in the source Host will get freed up. Bottom line don’t get too stingy with this value, the larger the value the faster Dremio will read source data and the more successful your RDBMS offload project is likely to become.
In the example below found that setting the value to 5000 was optimal on the 4 core 16 GB Oracle server used for this test.
Once all the Source properties are entered, click “Save” at the bottom right side of the screen and you now have immediate access to begin querying the Oracle tables.
This ease of access introduces another problem; and that is security. Without Dremio, RDBMS source system security could be physically controlled or at least managed through logins. Fortunately, Dremio respects the same security mechanisms already in place. In addition, Dremio introduces a thin “Sharing” security layer so if you are JOINing an Oracle table with a S3 file, the enterprise can be assured that consistent access controls are applied across the different data sources.
The Dremio “Sharing” capability can further restrict data visibility, by specifying which user or LDAP/AD membership groups can see which tables in Dremio. For example, in the view below, we can see all the tables which the Dremio administrator has access:
However, by enabling “sharing” for our “Data Analyst” user “da” to have view-only permissions on the Oracle “call_center_analytics” related tables. In the screen below we see that 2 users “Mark” and “da” have access to the Call_Center_Analytics space.
Now when our “da” user logs in they can only view data for the tables in the “Call_Center_Analytics” workspace.
The enterprises governance team further can assure that the “da” user is only accessing the CallCenter table by leveraging the “Jobs” page. From the “Jobs” page we have the ability to search for all queries performed by a user, ensuring that the enterprise access guidelines are being followed even if those rules are not consistently implemented in all underlying data sources.
As we see below, our “da” user can access the CallCenter table.
But, our “da” user CANNOT access any other of the other tables and based on either end user id or LDAP group membership will get a permissions denied error if they attempt access to other tables.
Then, click “Reflections” followed by “Raw Reflections” from the menu to begin defining our reflection. We want all of the dataset columns accelerated, so all columns are selected for “Display” as shown below.
Click “Save” and Dremio will begin creating the reflection which depending on the dataset can take seconds to minutes.
For a deeper treatment of the topics in this post, download Apache Iceberg: The Definitive Guide by Tomer Shiran, Jason Hughes, and Alex Merced, free from Dremio.
To see these ideas applied in practice, explore how Dremio approaches the open data lakehouse.
Deploy agentic analytics directly on Apache Iceberg data with no pipelines and no added overhead.
By unifying data from diverse sources, simplifying data operations, and providing powerful tools for data management, Dremio stands out as a comprehensive solution for modern data needs. Whether you are a data engineer, business analyst, or data scientist, harnessing the combined power of Dremio and Apache Iceberg will undoubtedly be a valuable asset in your data management toolkit.
This blog helps you learn about table-driven access policies in Dremio Cloud and Dremio Software v24.1+.
Dremio Arctic bring new features to Dremio Cloud, including Apache Iceberg table optimization and Data as Code.
