Error 5450

Naveen_Kumar

when i am trying to build a view table i am getting an error like -"ERROR 5450: View definition can not contain duplicate column names "e.ed.emalil". there are two key_name as "e.ed.email" and "e.ed.Email" and i have to keep both names. thank you

Abhishek_Rana

Hi,

Can you please post the statement causing error while creating view?

Also let us know the structure of "ed" table?

Regards'

Abhishek

Naveen_Kumar

 select build_flextable_view('n_mergedlog');
ERROR 5450:  View definition can not contain duplicate column names "e.ed.email"

Naveen_Kumar

n_mergedlog is a flex table. so i computed keys table first then when i tried to build a view i got these error. bcoz n_mergedlog_Keys  have many same keys in lower and uper form both. 
i hope u r  getting me. if not let me know.

Abhishek_Rana

I was thinking of you creating view with CREATE VIEW.

Sorry but,Can you explain your process of creating table & view more clearly.

Naveen_Kumar

i am creating table using "copy" after parsing a json file using fjsonparesr. since we dont know the definition of table yet, so i am using compute_flextable_keys_build_flextable_view. which take by default data types of all the keys from flex table and make a keys table then after we can make a view table . that is where i got stuck .bcoz keys table has duplicate keys. which view dosn't permit.

Shobhit_Garg

Hi Naveen,

Yes you would not be able to create a view with two same column names. But interesting to know why you have duplicate column names in your JSON file data

Thanks

Naveen_Kumar

Its predefined json file. and i dont know exactly why they have designed it like this.
thanks. 

[Deleted User]

JSON key names are case-sensitive, but SQL column names are not. I'm investigating the duplicate column name error (could you please post the specific version of Vertica you are using?) but for now, you can query for one column vs. the other by means of the "case_sensitive" parameter to MapLookup() ala:

SELECT MapLookup(__raw__, 'e.ed.email' USING PARAMETERS case_sensitive=True), MapLookup(__raw__, 'e.ed.Email' USING PARAMETERS case_sensitive=True) FROM n_mergedlog;

[Deleted User]

Greetings!
I'm unable to reproduce the error using the automated functions. Question--did you hand-edit your keys table (n_mergedlog_keys)? The automated compute_flextable_keys() function will de-dup key situations such as yours, but it's always possible to add a duplicate manually and cause build_flextable_view to fail.

If you want to explicitly include both casings in your view, there is no automated way to do in Crane, you can do so manually. We're also interested in hearing how you'd like handling case sensitivity to look like with these functions.
To manually add case-sensitive columns to your view:
1. Generate your keys table as normal with compute_flextable_keys('n_mergedlog')
2. Remove the row in n_mergedlog_keys for the column you want to make case sensitive
3. Build your view as normal with build_flextable_view('n_mergedlog')
4. Output the n_mergedlog_view creation DDL by calling: select export_objects('','n_mergedlog_view');
5. CREATE OR REPLACE n_mergedlog_view by running the same DDL plus two extra columns referencing: MapLookup(__raw__, 'e.ed.email' USING PARAMETERS case_sensitive=True)  and  MapLookup(__raw__, 'e.ed.Email' USING PARAMETERS case_sensitive=True)

Sorry for the problem.

Naveen_Kumar

Thanks for replies james. 
yea i did it manually. But for this i will have to find out all the duplicate keys initially . Since i dont know initially which type of keys are there. and we may have thousands of keys ,so how will we fine all the duplicate keys . thats y i was looking for some alternative. 
by the way i am using crane version.

[Deleted User]

          As it turns out, you have found a bug in this release's build_flextable_view() de-duping logic, but it's luckily something which can be worked around. We've identified this issue in the HP Vertica release and will assess fixing it in an upcoming release.
          Specifically, the bug will affect build_flextable_view() (and compute_flextable_keys_and_build_view()) when you have keys containing the same letters but different casing and different frequencies. A fix to get you going is to identify these duplicates and update their n_mergedlog_keys.frequency values to match in your n_mergedlog_keys table.

To identify the keys which are duplicates, you can always look in your vertica.log to view the query used to pick the keys on calls to build_flextable_view(). It will look something like:

SELECT distinct lower(key_name::varchar(128)) as key_name, CASE WHEN data_type_guess IS NULL THEN NULL ELSE trim(split_part(data_type_guess::varchar(200),'(',1)) END as data_type_guess_type_part, CASE WHEN data_type_guess IS NULL THEN NULL WHEN strpos(data_type_guess::varchar(200),'(') > 0 THEN trim(split_part(split_part(split_part(data_type_guess::varchar(200),'(',2),')',1),',',1))::int ELSE NULL END as data_type_guess_length_part_1,CASE WHEN data_type_guess IS NULL THEN NULL WHEN strpos(data_type_guess::varchar(200),'(') > 0 AND strpos(split_part(split_part(data_type_guess::varchar(200),'(',2),')',1) ,',') > 0 THEN trim(split_part(split_part(split_part(data_type_guess::varchar(200),'(',2),')',1),',',2))::int ELSE NULL END as data_type_guess_length_part_2,frequency FROM public.n_mergedlog_keys WHERE key_name is NOT NULL and LENGTH(key_name::varchar(129)) <= 128 ORDER BY frequency DESC,key_name LIMIT 1600;

This listing may be large, depending on your key space, so easier to wrap that query to find the duplicates by adding a prefix of "SELECT key_name, COUNT(1) AS NumDups FROM (" and a suffix, including the ending LIMIT 1600) of: ") AS keys_query GROUP BY key_name LIMIT 1600;" so that your new query looks like:

SELECT key_name, COUNT(1) AS NumDups FROM (SELECT distinct lower(key_name::varchar(128)) as key_name, CASE WHEN data_type_guess IS NULL THEN NULL ELSE trim(split_part(data_type_guess::varchar(200),'(',1)) END as data_type_guess_type_part, CASE WHEN data_type_guess IS NULL THEN NULL WHEN strpos(data_type_guess::varchar(200),'(') > 0 THEN trim(split_part(split_part(split_part(data_type_guess::varchar(200),'(',2),')',1),',',1))::int ELSE NULL END as data_type_guess_length_part_1,CASE WHEN data_type_guess IS NULL THEN NULL WHEN strpos(data_type_guess::varchar(200),'(') > 0 AND strpos(split_part(split_part(data_type_guess::varchar(200),'(',2),')',1) ,',') > 0 THEN trim(split_part(split_part(split_part(data_type_guess::varchar(200),'(',2),')',1),',',2))::int ELSE NULL END as data_type_guess_length_part_2,frequency FROM public.n_mergedlog_keys WHERE key_name is NOT NULL and LENGTH(key_name::varchar(129)) <= 128 ORDER BY frequency DESC,key_name) AS keys_query GROUP BY key_name LIMIT 1600;

This query may be eyeballed to find your duplicates, or modified to only return rows where NumDups >= 2.

To close the loop, once you've identified your duplicate column names, you can fix them via an UPDATE statement. For example, if your duplicate column(s) are called "aaa" (or "AAA", "AaA", etc.) then the following query will fix build_flextable_view() behavior for that column:

UPDATE n_mergedlog_keys SET frequency = (SELECT MAX(frequency) FROM n_mergedlog_keys WHERE key_name::varchar(128) ILIKE 'aaa') WHERE key_name::varchar(128) ILIKE 'aaa'; COMMIT;


Let me know if that doesn't work for you. Sorry for the issue!

Naveen_Kumar

This reply was created from a merged topic originally titled Error 5450. when i am trying to build a view table i am getting an error like -"ERROR 5450: View definition can not contain duplicate column names "e.ed.emalil". there are two key_name as "e.ed.email" and "e.ed.Email" and i have to keep both names. thank you

W_W

How about just giving us the new compute_flextable_keys query that will generate the keys table correctly and not generate duplicates, so we can just rebuild the whole table instead of fixing each duplicate one at a time?

Jack_1

Hi James,
We have the same case sensitive/duplicate key issue, any progress on a fix?
btw, the doc's are wrong as they consider the issue a warning, not an error.
https://my.vertica.com/docs/7.0.x/HTML/Content/Authoring/FlexTables/UpdatingFlexTableViews.htm
Mahalo, - JackG

Jack_1

Changed my mind. In my case the key gen is correct. Our provider should not be generating "duplicate" case'd keys. My solution is sed /s/"key"/"key"/gI;