What are you trying to do with the data? This will help narrow down options that exist for weather data? Does it need to be historical? what sort of data elements are required vs just nice to have? What is the outcome?
I have managed to get a forecast of each city by using Tableau Junkie Open Weather which provides a 5 working day forecast. I am having the opposite problem to you, could you advise how you got historic weather data from Big Query?