About sonnyheer

sonnyheer · ‎06-21-2017

@Sagar Morakhia I realize what I'm after is a row_num on A table. I actually want all from the join tables. but as more joins are added the main join key (e.g. A.key) keeps increasing. whats the format for joining to using unique A.key to others?? I'll try out some variations. Thanks for your help on this..

sonnyheer · ‎06-20-2017

@Sagar Morakhia That doesn't seem to work. based on doc it shows below, but that also requires a group by. I might be missing something. Select COUNT(B.b),B.key,ROW_NUMBER() OVER (partition by key) AS row_num from B)where row_num=1

sonnyheer · ‎06-19-2017

@Sagar Morakhia Yeah that makes sense. The difference being if I want to count some other column in B (e.g. B.b). thats where group by is the only option. Is there a way to get the highest count via row_num vs doing where row_num = 1? most of the time you want to join to a many table, but do something with the repeating column. In our case we want to count by key..

sonnyheer · ‎06-18-2017

@Sagar Morakhia Thanks for the response. Although that gives me the first matching row from B, what if I want other columns from B. for example B.b column is duplicated per A/B.key. Although with row_num = 1 - I only get the first one right? whats diff from just doing a GROUP BY on key? Is windowing faster? So in this regard windowing is helpful because I can control matching from the outer table (A) to all other child tables? e.g. the C join won't be based on the big table of A&B but instead on A.key. But this is still achievable with subquery and group by? correct me if i'm wrong...

sonnyheer · ‎06-16-2017

Thanks for the response. I decided to do inner select inside the left join to aggregate the records as needed. Its not clear how the windowing function helps yet (it will help if you can descirbe what you get at each stage using that function) - I will try it out and get back here.

sonnyheer · ‎06-15-2017

Hi all, We have multiple tables that need to be combined into a single table using left joins. There are many one to many relationships. Naturally - after the first join the subsequent join will produce duplicate rows. The end result is a massive table with mostly duplicates. I understand these can be removed easily in 2 ways. 1. doing a insert overwrite and selecting distinct rows. 2. group by on all final columns. Which of these is the optimal option? Is there a pattern in Hive that will allow adding in additional tables and removing duplicates per table (instead of all in the end)... Thanks in advance.

sonnyheer · ‎02-22-2017

Unfortunately I'm not able to upgrade. Somewhat of a hack, but i put together an 'expect' script. In case someone else is stuck in similar situation. 🙂 #!/usr/bin/env expect spawn ambari-server setup-security sleep 0.5 set timeout 3 expect { "*Choose one of the following options:*" { send "2\r" } timeout } expect { -re "Please provide master key for locking the credential store:" { send "dev\r" } timeout } expect { -re "Re-enter master key:" { send "dev\r" } timeout } expect { "Do you want to persist master key.*" { send "y\r" } } expect eof This is obviously not great, but does work given no other option ...

sonnyheer · ‎02-22-2017

Thanks for the response. we are actually stuck on 2.2.x

sonnyheer · ‎02-21-2017

Has anyone been able to automate ambari-server commands ? e.g. ssl / encryption setup etc.

sonnyheer · ‎02-21-2017

Nevermind...its only on server 🙂 it does seem to work, but doesn't show it in "op"

Online	Offline
Last Visited	‎01-09-2018 12:04 AM

Member Since	‎09-14-2016 05:23 PM
Last Visited	‎01-09-2018 12:04 AM
Posts	36
Kudos received	3

Cloudera Community

Re: Automatically calling ambari setup-security #2

Re: Handling Multiple joins creating duplicates

Re: Handling Multiple joins creating duplicates

Re: Handling Multiple joins creating duplicates

Re: Handling Multiple joins creating duplicates

Re: Handling Multiple joins creating duplicates

Handling Multiple joins creating duplicates

Re: Automatically calling ambari setup-security #2

Re: Automatically calling ambari setup-security #2

Re: Automatically calling ambari setup-security #2

Re: Auto restart ambari 2.2x