Here, I upload content in form of structured courses, tutorials, troubleshooting tips, quick how-to's on different topics of technology work stack.
I created this channel with the intent of adding value to technology engineering learning with a subtle approach of Why {conceptual understanding}, How {practical implementation} & What {impact and significance}. Professionally, I am a Data Engineer with a demonstrated history of working in the data analytics field.
I would appreciate your like and subscription, if the content was helpful for you. If you have any questions or video requests, please leave a comment and I will get back to you. I usually check and reply during the lunch hour or late evening on weekdays, and during the daytime on weekends.
I can also be contacted via any of my socials listed in the banner links!
Hi, you post informative videos. One Que. I have an Excel365 file which has data from the odbc connection, which updates automatically in scheduled intervals, how can I sync that Excel data into Google Sheets with auto updates?
Right now I am facing a issue while retrieving metadata about (region specific ) data it is showing some error which is saying “ Access Denied: Table famous-design-417916:region-us.INFORMATION_SCHEMATA: User does not have permission to query table famous-design-417916:region-us.INFORMATION_SCHEMATA, or perhaps it does not exist in location US. ”
In my experience using the load balancers this way is a very expensive solution. Im paying about 20 eur per month per website. Im looking for an alternative solution that is cheaper
how did you get that IP i mean from where, i know it need to be external IP but i have tried copy mine external IP and paste inside DNS A and it dosent work..
That's right! and i have mentioned that in video. When I created this, there was no recursive CTE in Bigquery, but google has now provided that functionality. So, that's the way to go.
Thank you much for the tutorial! Just what I need it. Now, how can you create an new table from the results in a way you can do it scheduled? I believe that would be using STORE PROCEDUREs with this scripting, but I don't know what would be the syntax to store the results into a final TABLE that can be reconstructed every time I run the PROCEDURE. I tried using SCHEDULED QUERIES but the scripting is not allowed there. Any help would be much appreciated.
Excellent video ma'am, really impressed with your teaching style. And a proposal 🤣 If your age is between 24 to 29 and ur unmarried, I am ready to marry you😉
I get an error. Invalid format specifier character "R" in FORMAT string. Anyone can help? DECLARE MONTHS STRING; SET MONTHS = ( SELECT CONCAT('("', STRING_AGG(DISTINCT cast(MONTH as string), '", "'), '")'), FROM ( select distinct date_trunc(jakarta_data_date, month) MONTH, from `data-gojek-id-mart.corporate_strategy.detail_booking` a where jakarta_data_date >= date_sub(date_trunc(current_date('Asia/Jakarta'), month), interval 3 month) order by 1 ) ); select MONTHS; EXECUTE IMMEDIATE format(""" SELECT * FROM ( select date_trunc(jakarta_data_date, month) AS MONTH, sa.service_area_name AS CITY, regexp_extract(UPPER(a.service_type_name),'(CAR|RIDE|FOOD)') as service_type_name, count(distinct order_no) AS BOOKING, from `data-gojek-id-mart.corporate_strategy.detail_booking` a left join `data-gojek-id-presentation.location.dim_service_area` sa on a.service_area_id = sa.service_area_id where jakarta_data_date >= date_sub(date_trunc(current_date('Asia/Jakarta'), month), interval 3 month) and (upper(a.service_type_name) like '%RIDE%' OR upper(a.service_type_name) like '%CAR%' OR upper(a.service_type_name) like '%FOOD%') and upper(sa.service_area_name) = 'BANDUNG' group by 1,2,3 ) PIVOT (SUM(BOOKING) FOR MONTH in(%s))""", MONTHS);
Thank you for the wonderful explanation. Is there a similar way by which we can dynamically aggregate string columns in bigquery sql? Usually we would just use Select STRING_AGG(col_1), col_2 From table1 Group BY col_2. What if the number of columns are increasing dynamically?
Hi Hasan, if you have a scenario where in you want to keep creating aggregate values for dynamic columns which can exist at any point of time then basically you need a query which adds that column formula at run time. You can achieve that by using combination of metadata table which will tell about new column and then add the same to dynamic SQL with string agg function. Do you have a use case where in your columns are increasing dynamically ?
@@datasudh I have a ‘contacts’ table in which new columns (containing information about the contacts like their age etc.) are being added everyday. These columns have either nulls or string values. I want to string aggregate all these columns and group by contact id so that I get one unique row per contact ID.
@@datasudh thanks for your reply. I have been trying to use INFORMATION_SCHEMA.COLUMNS with dynamic sql to solve this problem but have been unsuccessful so far.
