How to change table name a view references

Matt Millen shared this idea 5 years ago
Idea Logged

Hi,

I created a view in Yellowfin in my public redshift schema off a temporary table (let's call it public.tmp_view_1) so that I could get internal feedback on the reports created using the view. Now the format of the reports is finalized I'm going to create an automated job to write the data to production (call it prod.view_1).

What I'm concerned about is breaking all my reports if I just delete public.tmp_view_1 and drag in prod.view_1 in the Entity Relationships tab of the View Builder. I also have a lot of calculated fields that I think may get deleted.

Is there any way to switch the tables the view refers to over without breaking everything? In hindsight I should've just used a virtual table with select * from public.tmp_view_1 and the change would've been simple as the two tables are identical.

Thanks,

Matt

Replies (14)

photo
1

Hi Matt,

While it is possible to accomplish what you are seeking through back-end queries against the configuration database, I am hesitant to recommend this unless it is absolutely necessary.


Generally I would recommend testing these new tables in a mirrored schema of your current database. This will assure that the table names are all the same (something Yellowfin requires), and allow you to simply report the new reports/views on top of a datasource that points to the production database.


Is this something that you plan to do frequently?

Thanks,

Eric

photo
1

Normally I have used virtual tables for similar things, but that's generally because I do some manipulation of the data in the query. Because this would have been a simple select * from I pointed directly at the temp table. I certainly won't be making the same mistake again. I don't see a way of not breaking all my reports and losing the calculated fields at the view level right now.

Another issue I've seen is that if I have two tables in different schemas with the same name, i.e. qa.table_1 and prod.table_1, in the View Builder I can only see one of them (and not the one I want). Referring directly to tables seems like something to avoid completely

photo
1

Hi Matt,

It's recommended to Build Yellowfin Views from static datasources, so as not to modify the underlying data the metadata layer is dependent upon. If special circumstances require that you orchestrate your workflow in this way, I'd suggest working with a Consultant in determining an appropriate work flow as it pertains to the Yellowfin platform.

In regards to the second issue, can you confirm your datasource has the "Include Schema in SQL" option enabled?

Thanks,

Eric

photo
1

"Include Schema in SQL" option is checked, it seems like there is some deduplication at the table name level rather than at the combined schema and table level. I'll find a workaround for this by renaming tables in the db.

As we do not have a standard data model there is often a requirement to create reporting tables that will act as a view in yellowfin. In future I will write the data to a permanent schema to avoid the problems faced here. Having said that, functionality to switch out table names and update field mappings would have been very useful in development, switching between QA and prod is fairly commonplace

photo
1

Hi Matt,

If you are concerned your deduplication issues are defective behavior, we could take a look at that.

I understand the hassle of working around and modifying protocol. As I sad earlier, if this is something you would like to pursue, I can reach out to your Account Manager and put you in touch with our CSMs for a more in-depth consultation.

Thanks,

Eric

photo
1

Hi Matt,

Just wanted to check in, were you still seeking support for this issue?

Thanks,

Eric

photo
1

Hi Matt,

I'm going to go ahead and mark this ticket as Answered. Feel welcome to reach out with further inquiries.

Thanks,

Eric

photo
1

I have the same issue ... databases are dynamic sources in my view so why not give the possibility to change the schema and table name in a specific view.

In my case the schema and the tablenames are changed as the data stored in that table has changed (so the schema is changed and the table name also to reflect those changes). I also would like to change the schema and or the tablename without manually editing all kind of repo database tables.

I do understand that it should only be possible when you edit a view instead of cloning it. The same for changing the datasource. But not giving a possibility to change views and tablenames seems unnecessary restrictive to me.

photo
1

Hi JeRoen,

Thanks for reaching out to support.

In conversations with a colleague, he has had success renaming tables referenced by a view in the back-end, for example -


UPDATE public.reportview

SET viewname='"public"."CNV_campaign_data_extract"'

WHERE viewid = 89949

(please practice due diligence and have working backups when querying the configuration DB, as it is technically out-of-scope of support and can lead to irrevocable damage to the system)

So it should not be too difficult to implement this functionality in the frontend, I'm thinking a "rename" option when a table is selected in the View Builder -

5e20b1a4ebde33483d7c216d186ae522


Would this be the type of functionality you'd like to see in this case? Yellowfin does have the option to rename a table in a view in the case a table is not found, but perhaps in your case the original table still exists in the system.


82c62d2367f719e3e08902d4e67691c9


There's also an existing task to have the ability to be able to rename tables via web service, if that's more along the lines of what you're looking for. Let me know if there's a particular avenue you'd like to take here.

Thanks,

Eric

photo
1

Hello Eric,

Thank you for your extensive answer.

The added rename button (schema and tablename) would be perfect. It seams that all needed functionality is there already, only not available when the original table still exists. Or something like the code mode in YellowFin 9 so that the source XML for a certain table or view is also editable. This would make it also possible. The GUI way is offcourse the nicer way. But having code mode in view building would be nice too (I think about changing calculated fields without total rebuilding)... but that is a much broader issue.

I am not using webservices myself (only the GUI) but when the functionality off one is build then I think it should be relatively small effort to add the GUI or vice-versa.

Add my name to this functionality please. Thank you.

Kind Regards,

JeRoen

photo
1

Hi JeRoen,

Thanks for the reply. I've gone ahead and created a Developer task to look into the functionality you're seeking

Ability to Rename Existing Table in View Builder

Updates to the task will be provided here as they are available. I will in turn mark this ticket as Idea Logged for now; feel welcome to "like" the idea for increased visibility, or reply here if you have additional related inquiries.

Thanks,

Eric

photo
1

hi @YellowFin support,
the database management in our company want to change the name of the dBeaver/MySQL view that feed to the YellowFin views (which have several reports attach to it). How can we do it safely without the need to recreate the reports etc?

photo
1

Hi Vivianti,

Here's my testing -

1. Made reportheaderview Mysql view-

05be154a57f1d148161e4e88e2c505ff


2. made report and view from this single view

0333cdb53044f3c600f5aa72dc9f8118


3. renamed view -


af4a93032fa4bcd150ce6065fe4c5dd0


4. report is broken -

000a053fc3153c795cb2e1df13175559



5. did a clone edit -

90d052277f4807a1e0dad9e5e326aa1c

6. Used the "rename" function to update view name on prepare page-


53488aefb8b5c0a991327da3259f9aea

7. view sql as updated -

7b75b3c44c9bdb759dbba082fa578e2c


8. The clone now working, I published -

a7af1eac26cd2a09cc5aa088a932080d


9. Report is working again.

9c883dbf2934f364955c53ed8eae2238

Does this sound like a potential solution in your case? maybe you can test to confirm?


Thanks,
Eric

photo
1

Thank you so much Erik, will try

Vivianti Santosa

Sent from Mail for Windows

Leave a Comment
 
Attach a file