StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><strong>Generate DDL Statements for objects:</strong></p> <pre><code>SHOW TABLE {DatabaseB}.{Table1}; SHOW VIEW {DatabaseB}.{View1}; </code></pre> <p><strong>Breakdown of columns in a view:</strong></p> <pre><code>HELP VIEW {DatabaseB}.{View1}; </code></pre> <p>However, without the ability to create the object in the target database <code>DatabaseA</code> your don't have much leverage. Obviously, if the object already existed <code>INSERT INTO SELECT ... FROM DatabaseB.Table1</code> or <code>MERGE INTO</code> would be options that you already explored.</p> <p><strong>Alternative Solution</strong></p> <p>Would it be possible to have a stored procedure created that dynamically created the table based on the view name that is provided? The global application account would simply need privilege to execute the procedure. Generally the user creating the stored procedure would need the permissions to perform the actions contained within the stored procedure. (You have some additional flexibility with this in Teradata 13.10.)</p> <p>There are some caveats with this approach. You are attempting to materialize views that could reference anywhere from hundreds to billions of records. These aren't simple 1:1 views that are put on top of the target tables. Trying to determine the required space in the target database to materialize the view will be difficult. Performance can and will vary depending on the complexity of the view and the data volumes. This will not be a fast-path or data block optimized operation. </p> <p>As a DBA, I would be concerned with this approach being taken on by a global application account without fully understanding the intent. I trust you have an open line of communication with the DBA(s) involved for supporting this system. I'm sure there are reasons for your madness that can't be disclosed here.</p> <p><strong>Possible Solution - VOLATILE TABLE</strong></p> <p>Unless the implicit privilege for CREATE TABLE has been revoked from the global application account this solution should work. </p> <p>Volatile tables do not require perm space. There table definitions persist for the duration of the session and any data inserted into them relies on the spool space of the user who instantiated it.</p> <pre><code>CREATE VOLATILE TABLE {Global Application UserID}.{TableA_Copy} AS ( SELECT * FROM {DatabaseB}.{TableA} ) WITH NO DATA NO PRIMARY INDEX ON COMMIT PRESERVE ROWS; SHOW TABLE {Global Application UserID}.{TableA_Copy}; </code></pre> <p>I opted to use a Teradata 13.10 feature called <code>NO PRIMARY INDEX</code>. By default, <code>CREATE TABLE</code> AS will take the first column of the <code>SELECT</code> statement and make it the <code>PRIMARY INDEX</code> of the table. This could lead to skewing and perm space issues in your testing depending on the data demographics. You can specify an explicit <code>PRIMARY INDEX</code> on your own as you understand the underlying data. (See the DDL manuals for details on the syntax if you're uncertain.)</p> <p>The use of <code>ON COMMIT PRESERVE ROWS</code> for the intent of this example is probably extraneous. But in reality if you popped any data into that table for testing this clause would be beneficial in Teradata mode as the data would otherwise be lost immediately after the <code>CREATE TABLE</code> or any other data manipulation was performed against the volatile table. </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload