Replace fields with new fields from another source

Dean Flinter shared this question 6 months ago
Answered

Hi,

We currently have some fields that are defined by a virtual table.

We've decided to add these fields to our main fact table instead for performance reasons. As such, the current fields will become defunct

The fields are used in lots of reports so manually editing them to replace the old with the new is not really feasible.

Is there a faster way to replace/swap/switch the fields so that the new ones propagate to all the reports?


Thanks

Dean

Comments (10)

photo
1

Hi Dean,

This might be possible with a View replacement import, as Yellowfin will try to match fields based on their name and data type for the column, if it can't be done with the UUID. If the names and data types match, it should replace the old fields with the new, despite them being 'different'.

Let me know if that works for you. Obviously replacing a view with another view carries some risk, so please take every precaution to ensure all data is backed up, etc.

Kind regards,

Chris

photo
1

Thanks Chris

I'll test it out on our dev environment.

Fingers crossed!


Thanks

Dean

photo
1

Hi Dean,

Hope you're well!

Just checking in with this one, did you get a chance to test?

Kind regards,

Chris

photo
1

Hi Chris,

Not yet unfortunately

The change I want to make is part of a wider update to our tables and I'm still working out some issues on that side

It could be a week or 2 before I do so if you want, you can close the ticket and if needs be, I'll post back to open it back up


Thanks

Dean

photo
1

Hi Dean,

No worries, I'll close this one off for now. Feel free to open it later!

Kind regards,

Chris

photo
1

Hi Chris,

I managed to get this to work as per your suggestion. However, it took a bit of trial and error as the fields in question are used in quite a few calculated fields

I know this is something you don't normally recommend but is there a way to do this by editing the config DB? Perhaps whatever the application does when it replaces a field based on a name match

Even if you could point me to where I can find the field source information, that would be immensely helpful. I've tried looking around various tables but there is so many fields that I am having trouble finding it


Thanks

Dean

photo
1

Hi Dean,

It's reportfield and reportfieldtemplate that contain field data. The UUID for a field would be in reportfieldtemplate. However you certainly run the risk of breaking your views/reports by modifying them in this way.

When I want to find things in my Postgres Yellowfin database, I use a global search function that takes a string argument and returns a table containing some information on where you can find it in the database. I'll include it here, perhaps you might find it useful.

CREATE OR REPLACE FUNCTION global_search(	
	search_term text,
	param_tables text[] default '{}',
	param_schemas text[] default '{public}',
	progress text default null -- 'tables','hits','all'
	)
	RETURNS table(schemaname text, tablename text, columnname text, rowctid tid)
	AS $$
	declare
	query text;
	hit boolean;
	begin
	FOR schemaname,tablename IN
	SELECT t.table_schema, t.table_name
	FROM information_schema.tables t
	JOIN information_schema.table_privileges p ON
	(t.table_name=p.table_name AND t.table_schema=p.table_schema
	AND p.privilege_type='SELECT')
	JOIN information_schema.schemata s ON
	(s.schema_name=t.table_schema)
	WHERE (t.table_name=ANY(param_tables) OR param_tables='{}')
	AND t.table_schema=ANY(param_schemas)
	AND t.table_type='BASE TABLE'
	LOOP
	IF (progress in ('tables','all')) THEN
	raise info '%', format('Searching globally in table: %I.%I',
	schemaname, tablename);
	END IF;
	query := format('SELECT ctid FROM %I.%I AS t WHERE strpos(cast(t.* as text), %L) > 0',
	schemaname,
	tablename,
	search_term);
	FOR rowctid IN EXECUTE query
	LOOP
	FOR columnname IN
	SELECT column_name
	FROM information_schema.columns
	WHERE table_name=tablename
	AND table_schema=schemaname
	LOOP
	query := format('SELECT true FROM %I.%I WHERE cast(%I as text)=%L AND ctid=%L',
	schemaname, tablename, columnname, search_term, rowctid);
	EXECUTE query INTO hit;
	IF hit THEN
	IF (progress in ('hits', 'all')) THEN
	raise info '%', format('Found in %I.%I.%I at ctid %s',
	schemaname, tablename, columnname, rowctid);
	END IF;
	RETURN NEXT;
	END IF;
	END LOOP; -- for columnname
	END LOOP; -- for rowctid
	END LOOP; -- for table
	END;
	$$ language plpgsql;

Kind regards,

Chris

photo
1

Thanks Chris!

Yeah it's not something we're going to take lightly and we may not even go this route but I just wanted to see what would be involved doing it this way


Thanks

Dean

photo
1

Hi Dean,

That's no worries. Do let me know if you give it a try.

Kind regards,

Chris

photo
1

Hi Dean,


Hope everything is well with you.


Just wanted to let you know I'll be closing this request due to inactivity. However, if you ever wanted to re-visit this or have anything else I can help you with, please let me know.


Regards,

Chris