Vlookup unnested values using Bigquery
up vote
-2
down vote
favorite
I have two tables where table 1 contains two columns: a column with a list of URLs embedded in string values (each cell contains more than one URL), and a date column. I managed to extract all unnested URLs to Table 2. I need to vlookup the two tables to get the date for each scraped URL (as in Tabel 3)
Table 1 string | Date
______________
STRING 1 Date 1
STRING 2 Date 2
STRING 3 Date 3
Table 2 Scraped URL | string
______________________
scraped URL 1 STRING 1
scraped URL 2 STRING 2
scraped URL 3 STRING 3
Table 3 scraped URL | Date
_________________________
scraped URL 1 Date 1
scraped URL 2 Date 2
scraped URL 3 Date 3
sql
google-bigquery asked Nov 10 at 22:36
J.Doe
11
add a comment |
up vote
-2
down vote
favorite
I have two tables where table 1 contains two columns: a column with a list of URLs embedded in string values (each cell contains more than one URL), and a date column. I managed to extract all unnested URLs to Table 2. I need to vlookup the two tables to get the date for each scraped URL (as in Tabel 3)
Table 1 string | Date
______________
STRING 1 Date 1
STRING 2 Date 2
STRING 3 Date 3
Table 2 Scraped URL | string
______________________
scraped URL 1 STRING 1
scraped URL 2 STRING 2
scraped URL 3 STRING 3
Table 3 scraped URL | Date
_________________________
scraped URL 1 Date 1
scraped URL 2 Date 2
scraped URL 3 Date 3
sql
google-bigquery asked Nov 10 at 22:36
J.Doe
11
2
So what's the relation betweenurlanddate?
– dwir182
Nov 10 at 22:39
url are scraped from the string column in Table 1
– J.Doe
Nov 10 at 22:49
Yes, but where does the date come from?
– Alex
Nov 11 at 2:44
add a comment |
up vote
-2
down vote
favorite
up vote
-2
down vote
favorite
I have two tables where table 1 contains two columns: a column with a list of URLs embedded in string values (each cell contains more than one URL), and a date column. I managed to extract all unnested URLs to Table 2. I need to vlookup the two tables to get the date for each scraped URL (as in Tabel 3)
Table 1 string | Date
______________
STRING 1 Date 1
STRING 2 Date 2
STRING 3 Date 3
Table 2 Scraped URL | string
______________________
scraped URL 1 STRING 1
scraped URL 2 STRING 2
scraped URL 3 STRING 3
Table 3 scraped URL | Date
_________________________
scraped URL 1 Date 1
scraped URL 2 Date 2
scraped URL 3 Date 3
sql
google-bigquery asked Nov 10 at 22:36
J.Doe
11
I have two tables where table 1 contains two columns: a column with a list of URLs embedded in string values (each cell contains more than one URL), and a date column. I managed to extract all unnested URLs to Table 2. I need to vlookup the two tables to get the date for each scraped URL (as in Tabel 3)
Table 1 string | Date
______________
STRING 1 Date 1
STRING 2 Date 2
STRING 3 Date 3
Table 2 Scraped URL | string
______________________
scraped URL 1 STRING 1
scraped URL 2 STRING 2
scraped URL 3 STRING 3
Table 3 scraped URL | Date
_________________________
scraped URL 1 Date 1
scraped URL 2 Date 2
scraped URL 3 Date 3
sql
google-bigquery sql
google-bigquery asked Nov 10 at 22:36
J.Doe
11
asked Nov 10 at 22:36
J.Doe
11
edited Nov 10 at 22:53
asked Nov 10 at 22:36
J.Doe
11
asked Nov 10 at 22:36
J.Doe
11
asked Nov 10 at 22:36
J.Doe
11
11
2
So what's the relation betweenurlanddate?
– dwir182
Nov 10 at 22:39
url are scraped from the string column in Table 1
– J.Doe
Nov 10 at 22:49
Yes, but where does the date come from?
– Alex
Nov 11 at 2:44
add a comment |
2
So what's the relation betweenurlanddate?
– dwir182
Nov 10 at 22:39
url are scraped from the string column in Table 1
– J.Doe
Nov 10 at 22:49
Yes, but where does the date come from?
– Alex
Nov 11 at 2:44
2
2
So what's the relation between
url and date?– dwir182
Nov 10 at 22:39
So what's the relation between
url and date?– dwir182
Nov 10 at 22:39
url are scraped from the string column in Table 1
– J.Doe
Nov 10 at 22:49
url are scraped from the string column in Table 1
– J.Doe
Nov 10 at 22:49
Yes, but where does the date come from?
– Alex
Nov 11 at 2:44
Yes, but where does the date come from?
– Alex
Nov 11 at 2:44
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
Are you looking for a join?
select t2.scraped_url, t1.date
from table1 t1 join
table2 t2
on t2.scraped_url = t1.url
answered Nov 10 at 22:58
Gordon Linoff
744k32285390
I've tried this query and for some reason it is giving me this error 'Cannot access field scraped_url on a value with type STRING at [1:13]'
– J.Doe
Nov 17 at 12:15
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
Are you looking for a join?
select t2.scraped_url, t1.date
from table1 t1 join
table2 t2
on t2.scraped_url = t1.url
answered Nov 10 at 22:58
Gordon Linoff
744k32285390
I've tried this query and for some reason it is giving me this error 'Cannot access field scraped_url on a value with type STRING at [1:13]'
– J.Doe
Nov 17 at 12:15
add a comment |
up vote
1
down vote
Are you looking for a join?
select t2.scraped_url, t1.date
from table1 t1 join
table2 t2
on t2.scraped_url = t1.url
answered Nov 10 at 22:58
Gordon Linoff
744k32285390
I've tried this query and for some reason it is giving me this error 'Cannot access field scraped_url on a value with type STRING at [1:13]'
– J.Doe
Nov 17 at 12:15
add a comment |
up vote
1
down vote
up vote
1
down vote
Are you looking for a join?
select t2.scraped_url, t1.date
from table1 t1 join
table2 t2
on t2.scraped_url = t1.url
answered Nov 10 at 22:58
Gordon Linoff
744k32285390
Are you looking for a join?
select t2.scraped_url, t1.date
from table1 t1 join
table2 t2
on t2.scraped_url = t1.url
answered Nov 10 at 22:58
Gordon Linoff
744k32285390
answered Nov 10 at 22:58
Gordon Linoff
744k32285390
answered Nov 10 at 22:58
Gordon Linoff
744k32285390
answered Nov 10 at 22:58
Gordon Linoff
744k32285390
744k32285390
I've tried this query and for some reason it is giving me this error 'Cannot access field scraped_url on a value with type STRING at [1:13]'
– J.Doe
Nov 17 at 12:15
add a comment |
I've tried this query and for some reason it is giving me this error 'Cannot access field scraped_url on a value with type STRING at [1:13]'
– J.Doe
Nov 17 at 12:15
I've tried this query and for some reason it is giving me this error 'Cannot access field scraped_url on a value with type STRING at [1:13]'
– J.Doe
Nov 17 at 12:15
I've tried this query and for some reason it is giving me this error 'Cannot access field scraped_url on a value with type STRING at [1:13]'
– J.Doe
Nov 17 at 12:15
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53244110%2fvlookup-unnested-values-using-bigquery%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
So what's the relation between
urlanddate?– dwir182
Nov 10 at 22:39
url are scraped from the string column in Table 1
– J.Doe
Nov 10 at 22:49
Yes, but where does the date come from?
– Alex
Nov 11 at 2:44